COUNTIFs for multiselect drop downs

hlcrane

Hello,

I have a set of form responses where 350 colleagues have given their job level, and provided their 5 personal goals (in separate columns), and which of our organisational targets their goal aligns to.

They have selected the alignment to our organisational targets from a multiselect dropdown, and some people's goals align to more than one target.

I need to count how many times an organisational target has been selected in all the data. And also how many time it has been selected per job level.

I think I need to be using COUNTIFS and CONTAINS - but really struggling to get a formula to work. Would really appreciate some guidance!

Genevieve P.

Hey @hlcrane

Any time you're counting a value in a multi-select column, you'll want to use the HAS function to see if it has that selection along with others. 🙂

Here's more information:

HAS Function | Smartsheet Learning Center

https://help.smartsheet.com/function/has

Searches for an exact match of a single value, including if this value appears in a multicontact or multiselect dropdown column cells or ranges with other values. If HAS finds the exact match, it returns "True." Otherwise, it returns "False."

So for example, if I'm counting how many times "Apple" appears in a column along with other fruit, I would structure my countif like this:

=COUNTIF([Fruit Column]:[Fruit Column], HAS(@cell, "Apple"))

Or cross-sheet formula:

=COUNTIF({Fruit Column}, HAS(@cell, "Apple"))

Then per job level, I'd use COUNTIFS:

=COUNTIF([Fruit Column]:[Fruit Column], HAS(@cell, "Apple"), [Job Level]:[Job Level], "Level 1")

=COUNTIF({Fruit Column}, HAS(@cell, "Apple"), {Job Level}, "Level 1")

Cheers,

Genevieve

hlcrane

Thank you @Genevieve P. !

How does HAS vary to CONTAINS?

Thanks,

Holly

Genevieve P.

Hi @hlcrane

The structure of the two functions are opposite:

HAS(search in, value to look for)

CONTAINS(value to look for, search in)

For what they're doing, HAS needs an exact match and is built for multi-select cells/columns, whereas CONTAINS just looks to see if the cell contains that bit of text along with other text.

This does mean if you're using CONTAINS, depending on what you're looking for you may get more matches than you should. For example, if you're looking for "App" and you use CONTAINS, it will find "App", but it will also match "Apple", "Approve", "Whatsapp" since they all contain the phrase app. Does that make sense?

Cheers,

Genevieve