Counting number of contacts in a cell, "Allow Multiple Contacts", where cell contains 2+ contacts

Francisco Trejo

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 COUNTIF function always returns "1", regardless of how many contacts are in a given cell.

I am looking to return how many times a specific person appears.

Any ideas? Thanks in advance.

Genevieve P.

Hi @Francisco Trejo

I believe I answered you on this other thread, here!

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

Let me know if this works for you. If not, it would be useful to see a screen capture of the sheet you're referencing, but please block out sensitive data.

Cheers,

Genevieve

Genevieve P.

I'm glad I could help!

We've already stated the entire column range in the COUNTIF function: {Range 1}

So then for the "search_range" of HAS, the @cell tells the HAS to look for the "Contact Name" in each individual cell of the previously stated range.

Does that make a bit more sense?

Genevieve P.

Hi @Francisco Trejo

I believe I answered you on this other thread, here!

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

Let me know if this works for you. If not, it would be useful to see a screen capture of the sheet you're referencing, but please block out sensitive data.

Cheers,

Genevieve

Francisco Trejo

https://community.smartsheet.com/discussion/comment/315700#Comment_315700

Thank you so much this worked!!!! Do you happen to have an explanation as to why you use @cell here? I looked up the definition but still a bit confused on why its in the "search_range" of the HAS function.

Genevieve P.

I'm glad I could help!

We've already stated the entire column range in the COUNTIF function: {Range 1}

So then for the "search_range" of HAS, the @cell tells the HAS to look for the "Contact Name" in each individual cell of the previously stated range.

Does that make a bit more sense?