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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 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!