# Count the total number of choice in a multiple choice column

Options

Hi, after a long time googling I didn't find anything.

I want to have the total number of choice selected in a multiple-choice column

Ex (The formula should give a total of 5).:

ColumnA

---------------------Row1

Choice1

Choice3

---------------------Row2

Choice2

Choice5

---------------------Row2

Choice3

• ✭✭✭✭
Options

Hi Mathieu,

Here is base formula that should work for what you're asking:

=SUM(COUNTIF([ColumnA]:[ColumnA], CONTAINS("Choice1", @cell)), COUNTIF(ColumnA:[ColumnA], CONTAINS("Choice2", @cell)), COUNTIF(ColumnA:[ColumnA], CONTAINS("Choice3", @cell)), COUNTIF(ColumnA:[ColumnA], CONTAINS("Choice4", @cell)), COUNTIF(ColumnA:[ColumnA], CONTAINS("Choice5", @cell)))

This should work for 5 different options, if you have more/less all you need to do is add/remove COUNTIF statements as necessary, keeping an eye out on parenthesis and comma syntax.

Hope this helps! Please let me know if you have any questions.

Best,

Mike

• Options

Thank you for the fast reply Mike! that formula works fine, but we have over 100 different choices so I was looking for a more efficient alternative.

But sadly I think that doesn't exist.

• ✭✭✭✭✭✭
edited 03/03/20
Options

Right off, you could create a helper column that has

=COUNTM([Multi-Select]@row)

in it and then dragfill this down your new column. Then you could SUM the helper column.

=SUM([Helper Column]:[Helper Column])

EDIT: I have a different idea that doesn't require the helper column, but it requires testing. I will revisit this thread and let you know.

ADDITIONAL EDIT: Testing showed that it does work. You do not need a helper column for this. You can count the Multi-Select Column directly using a COUNTM nested inside of a SUM function.

=SUM(COUNTM([Multi-Select Column]:[Multi-Select Column]))

• ✭✭✭✭✭✭
Options

@Andrée Starå @Genevieve P Thought you two might be interested in the above little piece of info. You can specify a range inside of a COUNTM function and it will give you the total number of selections made across the range. It doesn't have to just be on the cell level...

@Mathieu Paquin You actually don't even need the SUM function. You can just use a COUNTM function and specify the range.

=COUNTM([Multi-Select Column]:[Multi-Select Column])

Options

@Paul Newcome thanks for the tag/headsup! This is wonderful.

• ✭✭✭✭✭✭
Options

@Paul Newcome Thanks! That is excellent!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!