Sheet Summary formula for counting unique values within a multi-select dropdown column?
I am trying to count the number of unique systems used within a workflow. I have dozens of possible internal systems listed within a restricted, multi-select, dropdown menu. As these systems are populated to the grid (sometimes multiple within a single field), does anyone have a formula that can generate the result below?
Box 1: Outlook
Box 2: Word
Box 3: Outlook, Word, Adobe
Box 4: Adobe
I'd like the sheet summary to say "3" because there are three unique systems being used (i.e. Outlook, Word, Adobe). Using COUNTM is giving me ""4" because it's counting Box 3 a distinct cell.
Any ideas?
Best Answer
-
You could insert another multi-select column and list all of the same possibilities as the main multi-select column (copy/paste should prevent typos). In any cell within this column (you should only need 1) you would enter:
=JOIN([Internal Column]:[Internal Column], CHAR(10))
Joining everything together and putting it into a multi-select column will remove any duplicates. You should then be able to use your COUNTM function on this cell.
@Leibel S: Here is another use case for counting unique values within a multi-select dropdown using your idea. Thought you might be interested to see it.
Answers
-
You could insert another multi-select column and list all of the same possibilities as the main multi-select column (copy/paste should prevent typos). In any cell within this column (you should only need 1) you would enter:
=JOIN([Internal Column]:[Internal Column], CHAR(10))
Joining everything together and putting it into a multi-select column will remove any duplicates. You should then be able to use your COUNTM function on this cell.
@Leibel S: Here is another use case for counting unique values within a multi-select dropdown using your idea. Thought you might be interested to see it.
-
@Leibel S Thank you!
-
Hi! I am trying out this formula, but the field where I a joining the cells that have multi-select dropdown is counting the text as 1.
Example -
Box 1 - January
Box 2 - January February March
Box 3 - February March November
Box 4 - [Join formula noted above] - January February March November (perfect)
Box 5 - CountM(Box 4) = result is 1, instead of 4.
Any help would be great!
-
Hi @Ruth wright
I hope you're well and safe!
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
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.
-
I actually just figured it out! I didn't realize my cell was not in multi-select when I applied the suggested formula. I had to re-read this article 3x for it to click!
Thank you for your answer!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!