How to count the total number of unique contacts from a contact list "allow multiple contacts"
Hi - I have a contact list column that allows multiple contacts per cell. I am trying to count the total number of unique contacts that are in this column. Attaching an image of the contact list column titled team. The answer based on the image would be 11 unique contacts.
Thanks in advance for the help!
Best Answer
-
=SUBSTITUTE(JOIN(Team:Team, CHAR(10)), ",", CHAR(10))
Answers
-
Create a helper column with the formula
=JOIN(Team:Team,Char(10))
Then the formula to count below in another column
=COUNTM(DISTINCT([Helper column]@row))
*Note this is if they are on the same sheet.
You could also do this on a sheet summary and do a cell for the helper column formula and reference your sheet summary in the other formula.
-
Hi Hollie - thanks for the help!
I tried this solution and it's giving me a count of 1... not sure why it won't separate the distinct text from the helper column list. Screenshot attached.
-
Make sure your helper column is set up as a multiple select column
-
I've adjusted the helper column, however, it's registering each team cell as 1 selection vs separating the contacts into individual selections and that's throwing off the distinct count.
-
I also noticed yours is separating by Comma's and mine separates with a line break when I use the =Join formula with CHAR(10)
Is both the helper and the team column set up as Multiple Select columns?
-
CHAR(10) is giving me a line break but it’s not separating by each contact, it’s separating by the entire content of each cell.
the team column is a contact list type column that allows for multiple contacts per cell.
-
The Contact type column does add a comma to the entries in a multi select column. That's part of the problem. I've been following and playing with this. Substitute can remove the comma but so far I haven't been able to combine it all in one formula.
Even removing the commas, I can't get the formula from @Hollie Green to work.
=COUNTM(DISTINCT([Helper column]@row))
-
=SUBSTITUTE(JOIN(Team:Team, CHAR(10)), ",", CHAR(10))
-
Great!! Thank you, Hollie and Ker9, this formula worked!
-
@Hollie Green - Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!