Group and Count Distinct Multiselect Entries

I am trying to count unique entries by a primary identifier using COUNTM.

When volunteers enter their activities they type in volunteer aliases. I created a column to be able to count volunteers by event (in the example below TaylorSwift is counted twice). How can I count volunteers for site1 without counting TaylorSwift twice?

EXAMPLE

i.e. Site 1 - TaylorSwift volunteered at both the Blood Drive and the Cleanup for site 1

Currently when I try to do a pivot or other formula it wants to total Site1's activities as 4, rather than 3.

WHAT I AM TRYING TO ACHIEVE - I want to only count TaylorSwift once when I count unique volunteers by the site level.

Is anyone able to help?

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would need to insert a helper column on the calculation sheet. You would need to make it a multi-select dropdown and then use

    =JOIN(COLLECT({Volunteers}, {Site}, @cell = [Site Name]@row), CHAR(10))


    Then you would use a basic COUNTM.


    Using the multi-select dropdown and the line break delimiter makes each individual selection in the source data a selection in the calculation sheet, and the multi-select dropdown type column will automatically remove any duplicates.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!