COUNTIF Formula in Multiple Dropdown Box but Exclude a Word

alwayslearning

I’m trying to create a COUNTIF from a multiple drop down column (referencing another sheet). Two words are similar: Active and Inactive.  

If I use CONTAINS, it counts both Active and Inactive: =COUNTIF({Certified Roster Range 1}, CONTAINS("Active", @cell))

If I use HAS, it counts when Active is the ONLY item listed in the drop down: =COUNTIF({Certified Roster Range 1}, HAS("Active", @cell))

What is the formula to count the word Active, even if there are other items listed in the drop down but exclude the word Inactive?

I really struggle with formulas. Thank you.

Paul Newcome

The HAS function should be working, but it looks like your syntax may be a little off (not sure why they flipped it)

=COUNTIF({Certified Roster Range 1}, HAS(@cell, "Active"))

Paul Newcome

The HAS function should be working, but it looks like your syntax may be a little off (not sure why they flipped it)

=COUNTIF({Certified Roster Range 1}, HAS(@cell, "Active"))

alwayslearning

You have rescued me with formula answers a few times before, Paul! I don't understand the language and all the symbols, punctuation, etc. But, everyone loves the high level reporting I use in sheets and on Dashboards. As always, THANK YOU! Have a great weekend.

Paul Newcome

Happy to help. 👍️

Here are a few of the resources I use...

Explanation of all functions:

https://help.smartsheet.com/functions

Explanation of all errors:

https://help.smartsheet.com/articles/2476176-formula-error-messages

And this one I haven't used in a while (less and less as experience grew). It is a sheet found in the template section (can just search for "formulas") that is essentially an interactive version of all functions.

It also helps to split things out and test things individually. To use your formula for example, you could go to the Functions list and see the proper syntax for the HAS function and that it outputs a true/false value. Knowing this along with the fact that checkboxes are checked/unchecked based on true/false, you could insert a temporary checkbox column next to the multi-select column, go to a row that you know should output a true value, and enter

=HAS([Column Name]@row, "Active")

If the box checks then great! We know we have the proper syntax and everything is reading as it should. Successful testing means we can delete this temporary column and go back to the target sheet.

We know we want a COUNTIF. So go ahead and type in the basic COUNTIF text from the function reference page. Yes. You are going to get an error, but at least now you have your proper syntax in the cell.

=COUNTIF(range, criterion)

Now you can simply highlight "range" and enter your range then highlight "criterion" and enter the appropriate criteria (which we already discovered with the HAS function on the source sheet testing bit). The only other change here would be replacing [Column Name]@row with @cell since we are using a cross sheet reference and want to count through the entire range on a cell by cell basis.

There is even an explanation/example of "@cell" in the template (although it is case sensitive and must be all lower case).

When you get into the more complex formulas that have multiple functions nested within, put each function in its own cell (when possible) so that you can make sure each piece is operating as it should. Then you can combine them all into the final, larger formula.