Dropdown Multi Select Countifs - how to count when more than one drop down present?
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?
Answers
-
@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"))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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
-
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)"))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks Jeff. I now get #INCORRECT ARGUMENT SET
-
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)"))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks Jeff, yes, that formula now works!
Thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!