Function to retrieve count in a multi-select column

Shafi M

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,

S

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.

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

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({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))