Count the total number of choice in a multiple choice column

Mathieu Paquin

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

Mike Raposo

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

Mathieu Paquin

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.

Paul Newcome

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

Paul Newcome

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

Genevieve P.

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

Andrée Starå

@Paul Newcome Thanks! That is excellent!