COUNTIFS - trying to make a formula that does the same work as the "Has Any of" filter from Reports
Im very new to writing Smartsheet formulas and have been scouring these forums the last few days to try and figure out how to get this to work
I am trying to write a formula that counts the rows with the following column criteria. Here is what I have so far but its not working
=COUNTIFS({Module}, OR(@cell = "AMBFO", @cell = "ARMG", @cell = "CWS"), {TrainingRdy}, <>"")
- Column - Module - I want the forumula to count rows that include any of the 3 values (AMBFO, ARMG, CWS). Any of those 3 values can exist in the same cell, it differs from row to row. What I dont want it to do count all the AMBFO's and all the ARMG's and all the CWS's and add that all together. I am essentially looking to recreate in formula form the same functionality that exists in Smartsheet Report Filter "has any of"
- Column - TrainingRdy - should simply not equal zero (i.e. count those rows that are not blank)
- If the answer is some combination of the HAS and OR functions, I haven't been able to figure that out yet.
Thanks for any advice folks can provide
Best Answer
-
You would use the HAS function:
=COUNTIFS({Module}, OR(HAS(@cell, "AMBFO"), HAS(@cell, "ARMG"), HAS(@cell, "CWS")), {TrainingRdy}, <>"")
Answers
-
I should also add, the "Module" column is a Dropdown, MultiSelect column type.
-
You would use the HAS function:
=COUNTIFS({Module}, OR(HAS(@cell, "AMBFO"), HAS(@cell, "ARMG"), HAS(@cell, "CWS")), {TrainingRdy}, <>"")
-
Leibel S, thank you so much! This worked perfectly.
I think I was using human logic versus computer logic when I tried to use HAS and OR statements before. I kept nesting the OR statements inside a HAS statement rather than the way your formula has it.
-
Any thoughts on if I could use a DESCENDANTS function within the HAS/OR portion of that formula? The three values I have listed are all children. The parent row is where I place the calculation. For the purposes of applying the formula to other parents or adding future children rows, it would be more efficient of I was able to essentially say "count the rows if they have any of the values from the children cells listed below"
Would HAS(@cell, DESCENDANTS) , or something like that, work?
-
I don't suppose there is an option for some kind of a NOT HAS caclulation? I fewer selections to exclude than include in my formulas. Just realized not excluding these selections is throwing off some of the totals.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!