Count with criterion.criteria within multiselect cell
I have multiselect cells in a sheet. I can use countm to count all selected items in a specific cell. However I need to count specific selections based on criteria. For example: A dropdown contains:
L1ABC, L1LMN, L1XYZ,L2ABC,L2LMN,L2XYZ, L3ABC,L3LMN,L3XYZ
One cell has: L1ABC,L2XYZ,L2ABC,L3XYZ
I want to search how many times a selection started with L2 was made? So the formula should return 2 for the above cell.
Best Answer

Hi Guys
I was excited to see that someone has put on a better example than me, as I love learning from other consultants too, however when I tested Nic's formula it didn't work properly for me.
So, I just want to clarify if the final formula works properly for you.
It appears in Nic one (please don't take this the wrong way!) that as soon as the Find command finds something that meets true within the multi select column, it stops looking for subsequent values. Hence it only finds 1 instance in each cell. But if you have more than 1 value in each cell that matches true, this formula will not be giving you a true count.
As you can see in this screen shot, the two formula's are asking the same thing, but one is returning 4 whereas the other is returning 7. Manually counting there are 7.
I really appreciate my version is not as succinct, and it will take longer to set up, but it is returning the true count!
This may not be an issue in your scenario, but please double check.
Kind regards
Debbie
Answers

I have a solution for you but it isn't terribly pretty... I can't find a way of nesting a LEFT type function inside a HAS function to only look at the first 2 letters of each code. I also can't find any wildcards that work with HAS. So I have achieved your requirement through a slightly long winded fashion, but it does work! :D
My Answer cell (returning 7) is looking at the column COUNT MULTI and returning all those that begin with L2.
My formula is:
=SUM(COUNTIF([COUNT MULTI]:[COUNT MULTI], HAS(@cell, "L2ABC")), COUNTIF([COUNT MULTI]:[COUNT MULTI], HAS(@cell, "L2LMN")), COUNTIF([COUNT MULTI]:[COUNT MULTI], HAS(@cell, "L2XYZ")))
I understand that it is fine when there are only 3 or 4 values in the list that meet the condition you want, but as it is a multiselect I would imagine the list of entries is kept relatively small (?) so this would/could work for you?
I hope it gives you something to work on.
Kind regards
Debbie

This might also work:
=COUNTIFS(Count Multi]:[Count Multi], FIND("L2", @cell) > 0)

Hi Guys
I was excited to see that someone has put on a better example than me, as I love learning from other consultants too, however when I tested Nic's formula it didn't work properly for me.
So, I just want to clarify if the final formula works properly for you.
It appears in Nic one (please don't take this the wrong way!) that as soon as the Find command finds something that meets true within the multi select column, it stops looking for subsequent values. Hence it only finds 1 instance in each cell. But if you have more than 1 value in each cell that matches true, this formula will not be giving you a true count.
As you can see in this screen shot, the two formula's are asking the same thing, but one is returning 4 whereas the other is returning 7. Manually counting there are 7.
I really appreciate my version is not as succinct, and it will take longer to set up, but it is returning the true count!
This may not be an issue in your scenario, but please double check.
Kind regards
Debbie

Thanks Debbie. Your answer worked. Thank you. It works if the options to search from are limited. I hope later Smartsheet adds criteria to CountM function

Hi there. Glad to have found this thread and would love a little help tweaking for a specific need. I have columns where cells contain up to several responses (multiselect). I need a formula for my sheet summary to count responses. For example, how many times has someone selected Urban (whether by itself of with other value/s). My formula is only counting when Urban appears by itself.
Population Density (column title)
Values (multiselect): Urban, Rural, Suburban, Tribal, Other
Appreciate any help. Formulas aren't my forte (yet).

Hi @Amy Koné
To count within a MultiSelect column you'll want to use the HAS function (see here).
For example:
=COUNTIF([Population Density]:[Population Density], HAS(@cell, "Urban"))
This will check each cell (using @cell) in the previously stated range to see if it HAS that text, either on its own or with other values. Keep in mind that HAS is specific to exactly the text specified, so "Urban" will only count for "Urban" and it won't count "Suburban" even though the text is contained in that word.
Let me know if this works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
Check out the Formula Handbook template!