How can you count someone assigned to a cell if there are multiple people assigned in the same cell

Evan Plante

Im using a metrics sheet to show home many people have RYGB symbols. However, there are some rows that have more that one person assigned to the task and it will not count their symbol if this is the case. Is there a formula that can count even if there is more than one person in the "ball in court" column?

Jeff Reisman

@Evan Plante

Try using HAS in place of CONTAINS:

HAS(@cell, "John LoVerde")

HAS will match complete individual values inside a multi-select cell.

FuWaye

@Jeff Reisman

Hi Jeff, I have a similiar issue, that I have a Contact field contains 3 diff people, and I want to count them seperately. So when I tried your tip of using HAS, for some reason it still doesn't seem to work. What am I missing? Please see the attached screencapture. Here is the formula:

=COUNTIF([HR Business Partner]:[HR Business Partner], HAS(@cell, "Rutherford, Jennifer"))

Note that I all tried to include email as well as name, and using wild card but all in vain. Any advise? Thanks much!

Partner.JPG

))

Jeff Reisman

@FuWaye

It seems that HAS does not really like Contact name values with a comma in them. You may have remove and re-add your contacts with first name last name.

FuWaye

Thanks for the prompt response, Jeff! Is there any other way to calculate it, other than changing the format for the contact? The strange thing is, it actually returns a value 2 for Jennifer, but 0 for Joetta, though neither is correct. I am just curious why and how it is calculated to get a value 2?

Jeff Reisman

@FuWaye Contact List columns can be funky. You can try temporarily changing the column to Text/Number, you'll see the values in there as text. You can make sure all the values for each instance are formatted the same. Then save and change back to Contact List.

FuWaye

@Jeff Reisman Got it. Thanks so much!

dojones

I had a similar problem trying to do lookups on contacts. I solved it by creating a helper column, defining it as a text /number, and setting it equal to the contact column. Then do the "contains" formula against the helper column.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/478666#Comment_478666

Ah yes, I have done the same in the past! Thanks for adding that!