Counting number of contacts in a cell, "Allow Multiple Contacts"

Dennis Wierzbicki · 06/19/20

Hello. I'm trying to count the number of people (contacts) who appear in cells in a contact list column, where I allow multiple contacts. Using a COUNT function always returns "1", independent of how many contacts are in a given cell.

I'm not looking to return how many times any specific person appears, only to count how many contacts are in a specific cell.

Any ideas? Thanks in advance.

Paul Newcome · 06/19/20

Try a COUNTM function.

=COUNTM([Contact Column]@row)

Paul Newcome · 06/19/20

Try a COUNTM function.

=COUNTM([Contact Column]@row)

Dennis Wierzbicki · 06/19/20

Ah, yes, of course. Forgot about that function. Thanks!

Paul Newcome · 06/19/20

Happy to help! 👍️

I have only ever seen it referencing multi-select dropdown type columns. Not a multi-select contact type column, so I had to test it myself before suggesting it. Haha

Dennis Wierzbicki · 06/19/20

Yep, it works. I had come up with a convoluted formula to count commas in a cell, then add 1, but the COUNTM function is a lot simpler :)

Paul Newcome · 06/19/20

I hope it wasn't too convoluted. Generally (when it isn't a multi select but a text string) I use something along the lines of...

=LEN(Column@row) - LEN(SUBSTITUTE(Column@row, ",", "")) + 1

If you count the number of characters and then subtract from that the number of characters after removing the delimiter, you can just add 1 to the result and get your answer.

Francisco Trejo · 01/11/22

https://community.smartsheet.com/discussion/comment/254198#Comment_254198

"I'm not looking to return how many times any specific person appears, only to count how many contacts are in a specific cell."

What if I AM trying to count how many times a specific person appears? I'm running into an error where if 2 contacts are in a cell it won't register either individual and just skips them. Is it possible?

Genevieve P. · 01/11/22

Hi @Francisco Trejo

Yes! You can use the HAS function with your criteria to see if a cell has this one selection (among others).

For example, if you were using a COUNTIF formula:

=COUNTIF({Range 1}, "Contact Name")

Then you would change it to this:

=COUNTIF({Range 1}, HAS(@cell, "Contact Name"))

See: HAS Function

Cheers,

Genevieve

Counting number of contacts in a cell, "Allow Multiple Contacts"

Best Answer

Answers

Categories