COUNTIF for dropdown column AND multiselect column
I am trying to count the number of times a variable shows up in a column that has both a High impact and is assigned to Department A. an example of a table would be as below:
Project 1, Department A & Department B, High
Project 2, Department A, High
Project 3, Department B & Department C, Medium
I tried using the following formula but it seems to have trouble reading multiselect column. Any ideas?
=COUNTIFS({AI Range 1}, HAS(@cell, High$1), {AI Range 2}, HAS(@cell, $Label@row)) where High 1 is at the top of a grid sheet and $Label@row = "Department A". In the example above, the value that should be returned is 2 as it should be able to read the value if it has just one value selected or multiple.
Best Answers
-
Hello David,
HAS is looking for an exact match. Since you have a multiselect, I'd recommend you use the CONTAINS function which looks to see if a string is within the referenced cell or cell range.
Try this to see if it works
=COUNTIFS({AI Range 1}, CONTAINS(High$1, @cell), {AI Range 2}, CONTAINS($Label@row, @cell))
Hope this helps!
Davin Vo - Sevan Technology
Smartsheet Platinum Partner
-
No problem!
And so for your example, it'll only count project 1? You'll add on a repeat of criterion2. Now instead of making it dynamic $Label@row, I switched it out for the static "Department A" and "Department B". You can make it dynamic again if you see fit.
=COUNTIFS({AI Range 1}, CONTAINS(High$1, @cell), {AI Range 2}, CONTAINS("Department A", @cell), {AI Range 2}, CONTAINS("Department B", @cell))
Davin Vo - Sevan Technology
Smartsheet Platinum Partner
Answers
-
Hello David,
HAS is looking for an exact match. Since you have a multiselect, I'd recommend you use the CONTAINS function which looks to see if a string is within the referenced cell or cell range.
Try this to see if it works
=COUNTIFS({AI Range 1}, CONTAINS(High$1, @cell), {AI Range 2}, CONTAINS($Label@row, @cell))
Hope this helps!
Davin Vo - Sevan Technology
Smartsheet Platinum Partner
-
Thanks @Davin Vo ! That is incredibly helpful, do you know how you would modify the above formula to have it CONTAIN two parameters in the second part (so for example if I wanted to see High Priority Projects that have both Department A and Department B)?
-
No problem!
And so for your example, it'll only count project 1? You'll add on a repeat of criterion2. Now instead of making it dynamic $Label@row, I switched it out for the static "Department A" and "Department B". You can make it dynamic again if you see fit.
=COUNTIFS({AI Range 1}, CONTAINS(High$1, @cell), {AI Range 2}, CONTAINS("Department A", @cell), {AI Range 2}, CONTAINS("Department B", @cell))
Davin Vo - Sevan Technology
Smartsheet Platinum Partner
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!