Function to retrieve count in a multi-select column

Options

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

«1

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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?

Options

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

• ✭✭
Options

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.

• ✭✭✭✭✭✭
Options

Try a COUNTM/COLLECT.

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

For the second formula, remove "@row".

• Options

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.

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!