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,

Find more posts tagged with

Formulas

Reports

Dashboards

Accepted answers

All comments

Carson Penticuff

Are the assignees designated with a multiselect dropdown?

Max329

No, with a Contact list column that allows multiple contacts per cell.

Screenshot 2023-06-09 at 11.03.42 AM.png

Carson Penticuff

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"))

Max329

That worked, thanks so much for your help Carson!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.