How to use a COUNTIFS formula that excludes multiple criterion in Multi-Select Dropdown column?

ALMF

I'm trying to count the total number of values in a Multi-Select Dropdown column when criteria in another Multi-Select Dropdown column is not present. For example, let's say I have a FRUIT column with Apple, Orange, Banana, and Kiwi values. I also have a STATUS column with Rotten, Fresh, Stolen, and Eaten values. In rows 1-6, I want to count how many times I found an Apple that was NOT Rotten. I can make it work with just one criterion excluded, but I can't find the right syntax for multiple criterion excluded. What's the best way to include multiple criterion in a NOT(CONTAINS expression? (The reason I am trying to EXCLUDE criteria is because my actual multi-select dropdown column has more than 50 values. I am trying to count occurrences in one column that excludes just a few qualifiers in another.)

Bassam Khalil

Hi @ALMF

Hope you are fine, please try to exclude the criteria as the following formula:

=COUNTIFS(Fruit:Fruit, CONTAINS("Apple", @cell), Status:Status, NOT(CONTAINS("Fresh", @cell)), Status:Status, NOT(CONTAINS("Stolen", @cell)), Status:Status, NOT(CONTAINS("Eaten", @cell)))

Sean Morgan

Hello @ALMF ,

Rather than contains, you may instead wish to use HAS as this looks for exact matches of the word: https://help.smartsheet.com/function/has

This works exactly like contains, except the range and criteria are switched around for instance: CONTAINS("APP", @cell) would instead be HAS(@cell "APP")

Let me know if you have any questions!

Regards

Sean

Bassam Khalil

Hi @ALMF

Hope you are fine, please try to exclude the criteria as the following formula:

=COUNTIFS(Fruit:Fruit, CONTAINS("Apple", @cell), Status:Status, NOT(CONTAINS("Fresh", @cell)), Status:Status, NOT(CONTAINS("Stolen", @cell)), Status:Status, NOT(CONTAINS("Eaten", @cell)))

ALMF

Great, thank you! That worked! However, my results are strange when I add additional words to the fruit column. The formula seems to pick up any instance of the word/letters I have in quotations, and not the exact word in quotations. For example, say I added a few rows with the word APP in my fruit column. When I use this formula below, it picks up all cells with the word "Apple" in this column also, I guess because it's looking for cells that contain the letters "APP" . How can I write this formula so that it only counts the cells that just say APP?

=COUNTIFS(Fruit:Fruit, CONTAINS("APP", @cell), Status:Status, NOT(CONTAINS("Fresh", @cell)), Status:Status, NOT(CONTAINS("Stolen", @cell)), Status:Status, NOT(CONTAINS("Eaten", @cell)))

Sean Morgan

Hello @ALMF ,

Rather than contains, you may instead wish to use HAS as this looks for exact matches of the word: https://help.smartsheet.com/function/has

This works exactly like contains, except the range and criteria are switched around for instance: CONTAINS("APP", @cell) would instead be HAS(@cell "APP")

Let me know if you have any questions!

Regards

Sean

ALMF

That worked! Thank you, both!