Using COUNTIFS to find a partial text match in 1 cell + other conditions

Max329

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,

Carson Penticuff

Are the assignees designated with a multiselect dropdown?

Max329

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

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!