Countif for multiple selection column

Cheryl Collins

If I take the 2nd Range 5 out (just before the OR), I get an incorrect argument error. Range 5 is the multi-select corrective action I am trying to interrogate.

Paul Newcome

Right. So you should have Range 5 only listed once and all of the different options you want to search for in that range listed in the OR.

Instead of

=COUNTIFS({Range 5}, "Option A", {Range 5}, OR(HAS(@cell, "Option B"), HAS(@cell, "Option C"))

You should have

=COUNTIFS({Range 5}, OR(HAS(@cell, "Option A"), HAS(@cell, "Option B"), HAS(@cell, "Option C"))

Cheryl Collins

So, this s the formula I have now:

=COUNTIFS({File Review Range 4}, "1", {File Review Range 2}, "RE", {File Review Range 5}, OR(HAS(@cell = "Please save a pdf copy of the CMI report in the matter compliance folder", OR(HAS(@cell = "Failure to obtain the correct client identification information", OR(HAS(@cell = "Source of funds checks not completed", OR(HAS(@cell = "Source of wealth checks not completed", OR(HAS(@cell = "Conflict checks were not carried out on appropriate person")))))))))))

But, it's still returning 10 instead of 1, so it looks like its counting all file reviews that have been done for RE rather than just the ones that have one of the options above.

Paul Newcome

The syntax is a little off. Try this...

=COUNTIFS({File Review Range 4}, "1", {File Review Range 2}, "RE", {File Review Range 5}, OR(HAS(@cell, "Please save a pdf copy of the CMI report in the matter compliance folder"), HAS(@cell, "Failure to obtain the correct client identification information"), HAS(@cell, "Source of funds checks not completed"), HAS(@cell, "Source of wealth checks not completed"), HAS(@cell, "Conflict checks were not carried out on appropriate person")))

A few things to note...

The correct syntax for the HAS function in this particular instance is

HAS(@cell, "specific text)

Notice the comma instead of the equals?

Also noticed that we need to close out each of the HAS functions before moving on to the next.

Finally, we only need to put the OR in one time and wrap it around ALL of the options for that one range.

OR(HAS(..........), HAS(..........), HAS(..........), HAS(..........))

Cheryl Collins

Hi @Paul Newcome

This has done the trick! Thanks so much for all your help on this one it was a complicated one in the end!! Thanks again

Cheryl

Paul Newcome

Happy to help. 👍️