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

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.)

Screen Shot 2021-02-06 at 11.53.34 PM.png

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.