COUNTIF CONTAINS not working for cell w/multiple contacts
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
-
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
-
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?
-
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
-
Would it help the formula (make it easier) if it wasn't allowing multiple email addresses?
-
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
-
@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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!