Hi Guys, Trying to create a formula to count multiple values in a column
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?
Answers
-
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?
-
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.
-
Lets try something like this...
=IF(OR(Citizen@row = "Yes", [ABN #]@row = "Yes", [Dropdown Column]@row <> "Partnership/Trust/Sole Trader"), "Approved for next round")
-
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")))
-
Excuse the spelling it been a long day😫
-
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?
-
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"?
-
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
-
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
-
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")
-
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")
-
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
-
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))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!