COUNTIF CONTAINS not working for cell w/multiple contacts

jmo

Hi team - I have a column formatted as a Contact List with multiple contacts per cell enabled:

In those cells are either full email addresses (ie: john.doe@email.com) or a Smartsheet contact (ie: John Doe).

I'm trying to do a COUNTIF formula that CONTAINS certain parts of the name so that it will look across both the full email address and contact list name to return a count. So if there are (2) john.doe@email.com and (3) John Doe contacts then it returns a count of 5.

This formula continues to return 0: =COUNTIF({Op Model EMG}, CONTAINS("Doe", @cell))

What am I missing?

Kelly Moore

Hey @jmoser

The problem you're experiencing is using CONTAINS with your multi select contact field.

CONTAINS will not work in a multi select Contact column. You can find more info here and here

You need to use either FIND or HAS

Note that FIND returns a number when true, not a "true" or "false" thus if using FIND it needs to be written as

=COUNTIFS({Op Model EMG}, FIND("Doe", @cell)>0)

cheers

jmo

Hi @KDM - I used =COUNTIFS({Op Model EMG}, FIND("Doe", @cell)>0) and it still returns 0.

It's not erroring, so that's good, but do you have any idea why I'm still getting 0?

Kelly Moore

What about this

=COUNTIFS({Op Model EMG}, FIND("DOE", UPPER(@cell))>0)

I got this one to work on my test sheet. There's also a LOWER() if you prefer it forced to lower case. Whatever is in your quotes will have to match the case.

Kelly

jmo

Would it help the formula (make it easier) if it wasn't allowing multiple email addresses?

Kelly Moore

That shouldn't be necessary. What happened when you tried the formula above? Did you also try it with HAS? The formula above (using UPPER) worked on my test sheet in a multi select contact column.

Please advise if you're receiving a zero, a wrong value, or an error

Kelly

jmo

@KDM I'm hesitant to use the UPPPER/lower case option you provided since I have submission that can be either be lower or title case.

Still grappling with this one.

Kelly Moore

The UPPER() doesn't change the data in your sheet. It forces the value being evaluated within the formula to be UPPER case. The function is specifically designed to account for case variation within text. It won't edit or update anything within your sheet.

Or are you concerned it will only count the Upper case cells in your sheet- No, this function forces all text to Upper case, thus eliminating the variation of upper/lower case within cells.