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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.

  • 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å
    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!

    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.

  • 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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!