Count with criterion.criteria within multi-select cell

I have multi-select 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:

L1-ABC, L1-LMN, L1-XYZ,L2-ABC,L2-LMN,L2-XYZ, L3-ABC,L3-LMN,L3-XYZ

One cell has: L1-ABC,L2-XYZ,L2-ABC,L3-XYZ

I want to search how many times a selection started with L2 was made? So the formula should return 2 for the above cell.

Find more posts tagged with

Accepted answers

Debbie Sawyer

@Amr Ismail and @Nic Larsen

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

All comments

Debbie Sawyer

@Amr Ismail

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, "L2-ABC")), COUNTIF([COUNT MULTI]:[COUNT MULTI], HAS(@cell, "L2-LMN")), COUNTIF([COUNT MULTI]:[COUNT MULTI], HAS(@cell, "L2-XYZ")))

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

Nic Larsen

This might also work:

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

Debbie Sawyer

@Amr Ismail and @Nic Larsen

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.

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

Amr Ismail

https://community.smartsheet.com/discussion/comment/271182#Comment_271182

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

Amy Koné

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 (multi-select). 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 (multi-select): Urban, Rural, Suburban, Tribal, Other

Appreciate any help. Formulas aren't my forte (yet).

Genevieve P.

Hi @Amy Koné

To count within a Multi-Select 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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.