Hi Guys, Trying to create a formula to count multiple values in a column

Sean McNulty

Hi Guys, Trying to create a formula to count multiple values in a column that has been designed to capture multiple trades trying to use a count-if formulas but not having much luck any suggestions?

Paul Newcome

Can you provide more detail? Are you trying to count cells that contain 3 different options in each cell or are you trying to count cells that have at least one of three different options? Are the options in a multi-select column?

Sean McNulty

Thanks Paul, appreciate your insight in community.

What I am trying to do is create a formula that pulls certain criteria from say 4 cells and the outcome is an approval to push a row through a workflow automation.

=IF(Citizen102 = "Yes", "Approved for next round", IF([ABN #]102 = "Yes", "Approved for next round"))

This is working so far however I need to add another set from a column that has 3 drop down text options 2 of which we want and one we don't. EG Public Company and Private Company approved for next round but (Partnership/Trust/Sole Trader) deems the row not approved for next round.

Paul Newcome

Lets try something like this...

=IF(OR(Citizen@row = "Yes", [ABN #]@row = "Yes", [Dropdown Column]@row <> "Partnership/Trust/Sole Trader"), "Approved for next round")

Sean McNulty

Hi Paul,

Getting somewhere now didn't think of using @row but makes sense. however I need to be able to show if [Entity]@row = "Private Company or Public Compnay with both citzen and abn# being yes then the row is approved for next round.

However i then havbe to try and also ensure that if "Partnership/Trust/Sole Trader"is selected in that row thenit is not approved?

This is what I tried? but not working

=IF(OR(Citizen@row = "Yes", [ABN #]@row = "Yes", [Entity]@row <> "Private Company", "Public Company), "Approved for next round",[Entity]@row= "Partnership/Trust/Sole Trader", "Not eligible")))

Sean McNulty

Excuse the spelling it been a long day😫

Sean McNulty

Hi Paul.

=IF(OR(Entity@row <> "Partnership/Trust/Sole Trader", Citizen@row = "Yes", [ABN #]@row = "Yes"), "Approved for next round")

is what we are trying but when you select "Partnership/Trust/Sole Trader" it still show up in the column as approved for next round?

Paul Newcome

My apologies. I read "another set" as adding another OR criteria. Let me double check your criteria...

Citizen@row = "Yes"

and

[ABN #]@row = "Yes"

and

Entity@row <> "Partnership/Trust/Sole Trader"

Is that it? You need two "Yes" cells and the third is not "Partnership/Trust/Sole Trader"?

Or is it at least one "Yes" out of the first two and the third is not "Partnership/Trust/Sole Trader"?

Sean McNulty

Thanks Paul,

That's

right

Citizen@row = "Yes"

and

[ABN #]@row = "Yes"

and

Entity@row <> "Partnership/Trust/Sole Trader"

Need two "Yes" cells and the third is not "Partnership/Trust/Sole Trader" If though Partnership/Trust/Sole has been selected when the person completed the form I need the formula to then ensure the row is flagged as not eligible. issue I have at the moment is the"Partnership/Trust/Sole Trader are getting through the workflow approval as they have approved for next round?

I'm guessing its a pretty straight forward formula maybe I'm making it more difficult that it needs to be just not sure

Thanks Paul

Sean McNulty

Ok I think I have it

I changed the or to and an it seemed to work the only issue i have is that if a supplier has left the entity cell blank (didn't enter anything ) it is approving this vendor for next round. how do I add a rule to this to ensure if entity is blank that it cannot progress?

=IF(AND(Entity@row <> "Partnership/Trust/Sole Trader", Citizen@row = "Yes", [ABN #]@row = "Yes"), "Approved for next round")

Thanks

Paul Newcome

If ALL criteria needs to be met, then you would need to use an AND statement instead of an OR.

=IF(AND(Citizen@row = "Yes", [ABN #]@row = "Yes", [Dropdown Column]@row <> "Partnership/Trust/Sole Trader"), "Approved for next round")

Paul Newcome

My apologies. For some reason your last comment didn't load the first time I pulled up this thread.

=IF(AND(Entity@row <> "", Entity@row <> "Partnership/Trust/Sole Trader", Citizen@row = "Yes", [ABN #]@row = "Yes"), "Approved for next round")

Sean McNulty

Thanks Paul, legend!!

Was thinking ISBlank was needed as I couldn't get the """" to work but the formula you proved now works a treat.

Greatly appreciated

Paul Newcome

A blank is a double set of quotes with either one or no spaces in between, and <> is the same as "not equal to".

Entity@row <> ""

is much shorter (and has less parenthesis to get mixed up) than

NOT(ISBLANK(Entity@row))