Formula Help - Countif in Multi-select column

Andrea Mayer

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 multi-select 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 multi-select 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 multi-select dropdowns?

Thanks!

Andrea

 

 

Nikhil Chawla

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

Paul Newcome

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

Andrea Mayer

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!

Andrée Starå

Hi Andrea,

Have a look at these new functions as well. 



https://community.smartsheet.com/discussion/two-new-functions-released-countm-has

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

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.

Andrea Mayer

Thank you, Paul.  This is EXTREMELY helpful!

Paul Newcome

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. 

florida_lori

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 multi-select 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 multi-select.



Is there a way to do this?



Appreciate any help!!

Lori

Paul Newcome

Try something like this...

 

=COUNTIFS({Other Sheet Multi-Select Column}, COUNTM(@cell) > 1)

florida_lori

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

Paul Newcome

Happy to help! 

 

It worked in my testing, so definitely let me know if it is not working how you wanted it to.