Using COUNTIFS to find a partial text match in 1 cell + other conditions
Hi Smartsheet Community,
I'm having trouble with a COUNTIFS formula and hoping you can help. I'm trying to count all rows that are assigned to a specific person. Because it's possible to have multiple assignees for a task on my sheet, I need to do a partial match for the assignee name.
Here is the formula I've created:
=COUNTIFS({Range}, CONTAINS("Doug", @cell))
For some reason this formula is returning 0 instead of the correct value. Can someone please let me know what I'm doing wrong?
Thanks,
Answers
-
Are the assignees designated with a multiselect dropdown?
-
No, with a Contact list column that allows multiple contacts per cell.
-
Try using HAS instead of CONTAINS. You also do not need to use COUNTIFS since you are only testing one condition. COUNTIF is appropriate here.
=COUNTIF({Range}, HAS(@cell, "Doug"))
-
That worked, thanks so much for your help Carson!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!