Sheet Summary formula for counting unique values within a multi-select dropdown column?

Mark Stadolnik

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?

Paul Newcome

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.

Paul Newcome

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.

Mark Stadolnik

@Leibel S Thank you!

Ruth wright

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!

Andrée Starå

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!

Ruth wright

Hi @Andrée Starå

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!