Countif for multiple selection column

Cheryl Collins

Hi

I'm trying to find a formula that will count a particular option in a multiple select column on another sheet.

I've tried using CONTAIN, HAS and FIND but neither appear to work - I get an error or it just returns a zero, when I'm expecting 1. Any help would be greatly appreciated.

TIA

Cheryl

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

Paul Newcome

Which range is the dropdown?

Cheryl Collins

Hi @Paul Newcome

Sorry, it's range 5. Thanks

Paul Newcome

Ok. Did you try this...

=COUNTIFS({File Review Range 4}, "1", {File Review Range 2}, "RE", {File Review Range 5}, CONTAINS(@cell, "Please provide client with updated costs information"))

Cheryl Collins

Hi @Paul Newcome

Sorry, that didn't work, I'm still getting a return of zero when I'm expecting a 1. Below is the screen shot of the field that range 5 is looking up (highlighted cell)

But, I'm still getting a zero on my data sheet, using your formula:

Thanks

Cheryl

Paul Newcome

It is looking for an exact match. It loos like I may have missed the word "the" before "client".

Cheryl Collins

Hi @Paul Newcome

I've updated the text so it is an exact copy of what's on the originating sheet (see screen shot below) but, it's still returning a zero.

Sorry.

Also when I try this on another formula that looks at multiple options I get a #INCORRECT ARUGMENT error:

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

Cheryl

Paul Newcome

The problem with this formula:

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

Is that when you repeat the range and include different criteria, you are basically saying that the cell must be an exact match to all of those things at the same time which is impossible.

What exactly is in Range 4?

Cheryl Collins

Hi @Paul Newcome

Range 4 is the quarter number. How would I get it to check if the column (range 6) contains any of those selections? Or is that not possible?

Thanks

Cheryl

Paul Newcome

You would need to use an OR function:

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

Cheryl Collins

Hi @Paul Newcome

I have copied and pasted your formula into my sheet but it is still returning a zero when 1 is expected:

This is the record from range 5 below:

I noticed that it was also referring to File Review Range 6 which is a mistake on my part it should all the ORs should be referring to File Review Range 5. I have updated this on my formula but it still returns a zero instead of a 1.

Thanks

Cheryl

Paul Newcome

Is range 5 a multi-select column? If so, are you wanting cells that only have exactly those entries or any cells that have at least one of those entries?

Cheryl Collins

Yes, range 5 is a multi-select column. I'm wanting any cells that have at least one of those entries. I can share an anonymised version of the sheets, if that's helpful?

Thanks

Cheryl

Paul Newcome

My apologies. I completely forgot about that part. You will need to include HAS functions for each of the options inside of the OR function.

=COUNTIFS(..............OR(HAS(@cell, "option a"), HAS(@cell, "option b"), HAS(@cell, "option c")), .................)

Cheryl Collins

No worries, I've added the HAS into the formula but, alas I'm still getting a zero rather than the expected 1.

Thanks

Paul Newcome

I see you have {Range 5} entered twice. Should that first one be included in the OR function?