How to count the total number of unique contacts from a contact list "allow multiple contacts"

Frank Mattiace

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!

Hollie Green

=SUBSTITUTE(JOIN(Team:Team, CHAR(10)), ",", CHAR(10))

Hollie Green

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.

Frank Mattiace

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.

Hollie Green

Make sure your helper column is set up as a multiple select column

Frank Mattiace

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.

Hollie Green

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?

Frank Mattiace

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.

ker9

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))

Hollie Green

=SUBSTITUTE(JOIN(Team:Team, CHAR(10)), ",", CHAR(10))

Frank Mattiace

Great!! Thank you, Hollie and Ker9, this formula worked!

ker9

@Hollie Green - Thank you!