Countif for multiple selection column
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
Best Answer
-
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(..........))
Answers
-
Which range is the dropdown?
-
Sorry, it's range 5. Thanks
-
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"))
-
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
-
It is looking for an exact match. It loos like I may have missed the word "the" before "client".
-
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
-
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?
-
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
-
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")
-
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
-
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?
-
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
-
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")), .................)
-
No worries, I've added the HAS into the formula but, alas I'm still getting a zero rather than the expected 1.
Thanks
-
I see you have {Range 5} entered twice. Should that first one be included in the OR function?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!