Function to retrieve count in a multi-select column

Hi,

So I have a multi-select 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,

Find more posts tagged with

Accepted answers

Paul Newcome

Try the COUNTM function. It was designed specifically for this...

=COUNTM([Multi-Select Column]@row)

Paul Newcome

Yes. You can actually do without the extra column for COUNTM.

You can use something along the lines of...

=COUNTIFS([Multi-Select Column]:[Multi-Select 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.

All comments

Paul Newcome

Try the COUNTM function. It was designed specifically for this...

=COUNTM([Multi-Select Column]@row)

Shafi M

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

Paul Newcome

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])

Shafi M

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

Paul Newcome

Yes. You can actually do without the extra column for COUNTM.

You can use something along the lines of...

=COUNTIFS([Multi-Select Column]:[Multi-Select 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.

Shafi M

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

Paul Newcome

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).

Avantika M

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

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?

Genevieve P.

Hi @Avantika M

Actually in this instance you'll want to use COUNTIF with HAS, instead. HAS was created specifically for multi-select 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 per-criteria (so one to return a count for "A", another one for "B", etc).

Let me know if this works for you!

Cheers,

Genevieve

Noble B

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({Multi-Select Column}@row) to see if removing the dates would work, i keep getting an error.

Paul Newcome

@Noble B

Try a COUNTM/COLLECT.

=COUNTM(COLLECT({Type}, {Date}, >=Month@row, {Date}, <Month3))

For the second formula, remove "@row".

Korbi Kaufman

What if I want to count how many times each person is selected in a multi-select 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.

Paul Newcome

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

Korbi Kaufman

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

Capture.JPG

Paul Newcome

@Korbi Kaufman

=COUNTIFS([Contact Column]:[Contact Column], FIND("John Doe", @cell) > 0))

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.