COUNTIF CONTAINS not working for cell w/multiple contacts

jmo
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?

Answers

  • Kelly Moore
    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
    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
    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
    jmo ✭✭✭✭✭✭

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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 04/01/21

    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
    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
    Kelly Moore ✭✭✭✭✭✭
    edited 04/02/21

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!