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

06/19/20
Accepted

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.

Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Answer ✓

    Try a COUNTM function.

    =COUNTM([Contact Column]@row)

    thinkspi.com

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Answer ✓

    Try a COUNTM function.

    =COUNTM([Contact Column]@row)

    thinkspi.com

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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

    thinkspi.com

  • 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 NewcomePaul Newcome ✭✭✭✭✭

    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([email protected]) - LEN(SUBSTITUTE([email protected], ",", "")) + 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.

    thinkspi.com

  • "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?

  • 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

Sign In or Register to comment.