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

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?
Answers
-
Try using HAS in place of CONTAINS:
HAS(@cell, "John LoVerde")
HAS will match complete individual values inside a multi-select cell.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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!
))
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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?
-
@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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman Got it. Thanks so much!
-
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.
-
Ah yes, I have done the same in the past! Thanks for adding that!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!