Formula Help  Countif in Multiselect column
Hello 
Two formula questions:
1. I am trying to input a countif formula into a Summary field that will count how many times the name of a specific team appears in a column that uses a multiselect dropdown. This column also has some fields that are blank. Below is what I have tried and am receiving an 'Unparseable' error.
=COUNTIF(CONTAINS("HR" [Impacted Teams (Resources)]4:[Impacted Teams (Resources)]11))
2. I am also trying to input a countif formula into another Summary field that will count how many times a group of names of teams appears in a column that uses a multiselect dropdown. This column also has some fields that are blank in the column.
I'm not sure where to start with this second one.
Can I get some guidance on how to use the new contains formula with multiselect dropdowns?
Thanks!
Andrea
Comments

Hello Andrea,
1) Answer to the first formula question: Based on this formula, it seems like you are trying to search for HR only between Impacted Teams (Resources) cell 4 and 11.
If you would to keep search for HR only between those cells then use the below formula:
=COUNTIFS([Impacted Teams (Resources)]4:[Impacted Teams (Resources)]11, FIND("HR",@cell)>0)
If you want to search for HR anywhere in column titled "Impacted Teams (Resources)" then use the below formula:
=COUNTIFS([Impacted Teams (Resources)]:[Impacted Teams (Resources)], FIND("HR",@cell)>0)
2) Answer to the second formula question: I am a little confused on this, but if you are trying to see how many times HR and IT appears in column titled "Impacted Teams (Resources)" then you can use the below formula:
=COUNTIFS([Impacted Teams (Resources)]:[Impacted Teams (Resources)], FIND("HR",@cell)>0) + COUNTIFS([Impacted Teams (Resources)]:[Impacted Teams (Resources)], FIND("IT",@cell)>0)
Hope this is what you are looking for.
Best Regards,
Nikhil Chawla

Are you able to provide more detail as to what you are looking to accomplish with #2?

Thanks Nikhil, this worked perfectly for #1 and #2. For #2, that is exactly what I was trying to accomplish. I had 3 different teams I wanted counted from a multiselect dropdown.
For the future, can I ask for some clarification?
1. My team learned at the Smartsheet conference that the new CONTAINS formula could be used for this type of scenario. Is that not correct?
2. When is the ISBLANK function required?
I appreciate help with this. I don't know what I would do without the Smartsheet Community!

Hi Andrea,
Have a look at these new functions as well.
https://community.smartsheet.com/discussion/twonewfunctionsreleasedcountmhasI hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

1. CONTAINS can be used in place of the FIND function.
=COUNTIFS([Impacted Teams (Resources)]:[Impacted Teams (Resources)], CONTAINS("HR",@cell))
The main difference between CONTAINS and FIND is the output. FIND will generate a number based off of where within a text string the specified text is found. Thus the >0 addition. CONTAINS returns a true/false. There are a few other differences that can also come into play depending on your use such as FIND is case sensitive whereas CONTAINS is not, and CONTAINS does not work on contact type columns whereas FIND will.
.
2. The ISBLANK function comes into play when you are testing whether a cell (or data from a nested formula) is blank. The function will return a true value if the cell is blank or a false value if the cell is not blank.
.
.
Additional note:
Adding a bunch of COUNTIFS together can get rather bulky rather quickly. Especially if your column names are long. It also opens up room for typos. You can instead use an OR function in your criteria portion to look for multiple values across the same range.
.
Using the FIND function as you are already familiar with it (keeping in mind that it IS case sensitive), lets work a little backwards. We know we want to find 3 different team types.
FIND("HR", @cell) > 0
FIND("IT", @cell) > 0
FIND("Finance", @cell) > 0
Now we look at the OR
OR(logical statement 1, logical statement 2, logical statement 3)
OR(FIND("HR", @cell) > 0, FIND("IT", @cell) > 0, FIND("Finance", @cell) > 0)
Now we can use that OR as the criteria in our COUNTIFS so that we are only entering one formula with one range. This ensures as much consistency as possible.
=COUNTIFS([Impacted Teams (Resources)]:[Impacted Teams (Resources)], OR(FIND("HR", @cell) > 0, FIND("IT", @cell) > 0, FIND("Finance", @cell) > 0))
.
Using 2 or even 3 COUNTIFS added together may not be too bad, but if your columns names (and thus your range) is a pain to type out or is very specific or you have numerous differnet things you are looking for across the range, this will make it so you only have to type it right once.
.
Using OR:
=COUNTIFS([Impacted Teams (Resources)]:[Impacted Teams (Resources)], OR(FIND("HR", @cell) > 0, FIND("IT", @cell) > 0, FIND("Finance", @cell) > 0))
.
Adding individual formulas together:
=COUNTIFS([Impacted Teams (Resources)]:[Impacted Teams (Resources)], FIND("HR",@cell)>0) + COUNTIFS([Impacted Teams (Resources)]:[Impacted Teams (Resources)], FIND("IT",@cell)>0) + COUNTIFS([Impacted Teams (Resources)]:[Impacted Teams (Resources)], FIND("Finance",@cell)>0)
.
Both of the above formulas accomplish the same thing and are looking at the same range, but you can see how much of a difference there is just in 3 different teams.

Thank you, Paul. This is EXTREMELY helpful!

Happy to help!
I know that sometimes what works for one particular use case isn't necessarily a good fit for a very similar use case or it doesn't scale very well.
Options, options, and more options.

Hi  I am trying to use the COUNTIF WITH THE COUNTM function. I want to get a count of how many cells in a column have more than one value selected from the multiselect option.
This would be a formula in a different sheet so different than Andrea's example above where she has another column next to her multiselect.
Is there a way to do this?
Appreciate any help!!Lori


Thank you Paul. I will try that and let you know if it works!

Happy to help!
It worked in my testing, so definitely let me know if it is not working how you wanted it to.
Help Article Resources
Categories
Check out the Formula Handbook template!