Group and Count Distinct Multiselect Entries

NOBLAURA

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?

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.