Combine COUNTIF and OR


Trying to count "Activity" by State, with the Activity Column formatted as a drop down column with multiple selections.

Formulas are on a separate worksheet, and I am referencing cell ranges. I have all states listed in one column, and referencing the "State" range an

Individually, the two formulas below work successfully. However, when I try to combine, no luck.

=COUNTIFS({State}, State2), (returns 6

=COUNTIFS({Activity}, OR(@cell = "ASA", @cell = "ASA (Multiple Systems)"))) (returns 104)

Total is 4 as "State@" has two ASA and two ASA Multiple Systems.

Any assistance is GREATLY APPRECIATED!


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Smarty

    I believe you have an ill-placed parenthesis in your screenshot formula

    See if this works for you. Are you intentionally calling out State2 or would State@row work for you to make it dynamic?

    =COUNTIFS({State}, State2, {Activity}, OR(@cell = "ASA", @cell = "ASA (Multiple Systems)"))

    Will this work for you?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!