Dropdown Multi Select Countifs - how to count when more than one drop down present?

darrink

I'm trying to use a countifs in order to create a summary of something that uses a value from one column (that has a single select dropdown) as a qualifier to count the row IF another column has a specific value, "dropdownvalue_2".

The issue I'm running into is that when I have more than one value in the multi-select column and as a result, it doesn't find a match because there are two values in there (one or more selections from the dropdown). In order for this to count properly, it has to contain "dropdownvalue_2" and I don't care if it also has other values such as "dropdownvalue_1" and "dropdownvalue_3".

=COUNTIFS([first_column]:[first_column], "status", [second_column]:[second_column], "dropdownvalue_2")

Any ideas on how to solve this?

Jeff Reisman

@darrink The answer is in the HAS function. (The help page for HAS is awful, I've been trying for months to get Smartsheet to update it.)

HAS was designed to work with multi-select cells. It will find the exact match for an individual value from a multi-select cells containing one or more selections.

=COUNTIFS([first_column]:[first_column], "status", [second_column]:[second_column], HAS(@cell = "dropdownvalue_2"))

darrink

Thanks Jeff... I put it in and it looks like I may be missing something still.

=COUNTIFS([Quote Validation Step]:[Quote Validation Step], "Approved / Validated", [Eligible Deal Type]:[Eligible Deal Type], HAS(cell = "Managed Services (propose & quote)"))

I get #UNPARSEABLE

Jeff Reisman

@darrink

You missed the "@" before cell=

=COUNTIFS([Quote Validation Step]:[Quote Validation Step], "Approved / Validated", [Eligible Deal Type]:[Eligible Deal Type], HAS(@cell = "Managed Services (propose & quote)"))

darrink

Thanks Jeff. I now get #INCORRECT ARGUMENT SET

Jeff Reisman

https://community.smartsheet.com/discussion/comment/328219#Comment_328219

Sorry, that one is my mistake! Guess I was going too fast.

There should be a comma in place of the = sign in the HAS function. The = sign is what's causing that error. BTW, the links in my signature got to the Functions List and help pages, and to the list of formula error messages and their explanations. These can be super helpful.

=COUNTIFS([Quote Validation Step]:[Quote Validation Step], "Approved / Validated", [Eligible Deal Type]:[Eligible Deal Type], HAS(@cell, "Managed Services (propose & quote)"))

darrink

Thanks Jeff, yes, that formula now works!

Thank you so much!