COUNTIFS - trying to make a formula that does the same work as the "Has Any of" filter from Reports

Eric Disque

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

Leibel S

@Eric Disque

You would use the HAS function:

=COUNTIFS({Module}, OR(HAS(@cell, "AMBFO"), HAS(@cell, "ARMG"), HAS(@cell, "CWS")), {TrainingRdy}, <>"")

Eric Disque

I should also add, the "Module" column is a Dropdown, MultiSelect column type.

Leibel S

@Eric Disque

You would use the HAS function:

=COUNTIFS({Module}, OR(HAS(@cell, "AMBFO"), HAS(@cell, "ARMG"), HAS(@cell, "CWS")), {TrainingRdy}, <>"")

Eric Disque

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.

Eric Disque

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?

Tina Rustvold

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.