Count the total number of choice in a multiple choice column
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
Answers
-
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
-
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.
-
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]))
-
@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])
-
@Paul Newcome thanks for the tag/headsup! This is wonderful.
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!