COUNTIFs for multiselect drop downs
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!
Answers
-
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:
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!