Function to retrieve count in a multiselect column
Hi,
So I have a multiselect column which upto 7 selections currently. What I am trying to do is have a bar chart of count based on the number of selections in each cell. To give an example.
row 1 : A, B, C,D,E,F,G
row 2: B,C,D,E
row 3: A,C,D
row 4: C,E,F
row 6: A
So what i would like to do is create a graph like
number of occurance with 7 values selected:
number of occurance with 6 values selected:
number of occurance with 5 values selected:
etc..
Hope that above was not cryptic. I have been trying some options with countifs but could not get it working yet.
Any help would be much appreciated.
Thanks,
S
Best Answers

Try the COUNTM function. It was designed specifically for this...
=COUNTM([MultiSelect Column]@row)

Yes. You can actually do without the extra column for COUNTM.
You can use something along the lines of...
=COUNTIFS([MultiSelect Column]:[MultiSelect Column], COUNTM(@cell) = #)
Just change the column names to cover your actual range, and change the # to the number you want to use. Using your example above, that's where the 7 would go.
Answers

Try the COUNTM function. It was designed specifically for this...
=COUNTM([MultiSelect Column]@row)

Thanks Paul, that worked. I created a column with the CountM function and then used a countif to do the job! Thanks again!

What was the purpose of the COUNTIF if you don't mind me asking?
I ask because if you wanted to count the number of selections across multiple cells all at once, you can use a range inside of the COUNTM function as well. OFr example...
=COUNTM([Column Name]:[Column Name])

Actually what I am trying to do is find out how the number of selections are grouped in a column. There are 7 possible selections.
So we can have the user select 1 or all of the selections (7). So countm([column],@row) will tell me the number of cells that has say 4 selections in that row. This i will update in a separate column called 'COUNTM'
Then I can just have the formulate ( COUNTIF([COUNTM)]:[COUNTM], 1) to find count of rows that has selections=1 and i can use COUNTIF([COUNTM)]:[COUNTM], 7) to find rows that has selections=7
Hope that made sense. If there is a better way let me know; i started looking into formulas as recent as yesterday:)
Thanks,
Shafi

Yes. You can actually do without the extra column for COUNTM.
You can use something along the lines of...
=COUNTIFS([MultiSelect Column]:[MultiSelect Column], COUNTM(@cell) = #)
Just change the column names to cover your actual range, and change the # to the number you want to use. Using your example above, that's where the 7 would go.

Brilliant, i was thinking about embedded CountM within Countifs but chickened out.! Thanks Paul, your suggestion works like a charm!

Happy to help! 👍️
And don't be afraid to try something new or ask about something. Even if it seems like a long shot, it may be something that has already been done and help is only a few keystrokes away (just like this).

Hi.. a follow up question on this one..
Do the same formulas work if i want to calculate (for a report) how many times a certain value has been selected, when the column type is multiselect?
For example , I have a multi select column with dropdown values A, B, C, D and so on. I want to summarize the count for each of these values into a report. My report should be able to capture the count so I have A  20, B  5, etc..Can i use COUNTM for that?

Hi @Avantika M
Actually in this instance you'll want to use COUNTIF with HAS, instead. HAS was created specifically for multiselect columns, to see if the cell has a specific value within it.
Try something like this:
=COUNTIF([Dropdown Column]:[Dropdown Column], HAS(@cell, "A"))
You'll need to replace [Dropdown Column] with your column name, and create one formula percriteria (so one to return a count for "A", another one for "B", etc).
Let me know if this works for you!
Cheers,
Genevieve

Hi
I have a similar question, does this work when referencing another sheet? i would like to count the number of selection per sell within a specific date. i tried using =COUNTM({Type}, {Date}, >=Month@row, {Date}, <Month3). i also tried =COUNTM({MultiSelect Column}@row) to see if removing the dates would work, i keep getting an error.

Try a COUNTM/COLLECT.
=COUNTM(COLLECT({Type}, {Date}, >=Month@row, {Date}, <Month3))
For the second formula, remove "@row".

What if I want to count how many times each person is selected in a multiselect column (i.e.: out of 50 projects, John is collaborating on 40, Joe on 35, etc.)? I want to create a bar graph showing how many projects John is working on, etc.
Because the actual person is selected (actual contact info of that person in Smartsheet), I can't get it to count properly.
=COUNTIF([Dropdown Column]:[Dropdown Column], HAS(@cell, "A")) does not work for me.
Any help is appreciated.

@Korbi Kaufman Is it a dropdown column or a contact column?

Ummmm...a dropdown contact column...?

=COUNTIFS([Contact Column]:[Contact Column], FIND("John Doe", @cell) > 0))
Help Article Resources
Categories
Check out the Formula Handbook template!