COUNTIF for dropdown column AND multiselect column

davidhorton45

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.

Davin Vo

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

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

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!

davidhorton45

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

Davin Vo

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