Question

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 · Accepted Answer

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 · Accepted Answer

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