Count & Contains

I got this to work for part of my issue however, I have a sheet that has a "Action Required By" multi-select contact column. I just want to count all the times that a name appears in this column. However, no matter what I try I am getting either a 0 or 1 and the name appears more than 5 times..

Here are the formulas I have tried

=COUNT(FIND("Jane Doe", {1 - D1 Builder Pursuits Range 1}, @cell > 0))

=COUNT(CONTAINS("Jane Doe", {1 - D1 Builder Pursuits Range 1}))

=COUNTIFS({1 - D1 Builder Pursuits Range 1}, "Jane Doe", {1 - D1 Builder Pursuits Range 1}, CONTAINS("Jane Doe", @cell))

I have tried every iteration of these formulas and none of them are working. They either return a result of 0 or 1. What am I doing wrong?

Thank you kindly

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Ashlee Chartier

    The FIND function returns a position value in a text string, not a true or false, so you must use criteria that makes the statement true or false. If the text you need is found, the position value will be greater than zero.

    Try this

    =COUNTIFS({1 - D1 Builder Pursuits Range 1}, FIND("Jane Doe", @cell) > 0)

    Does it work for you?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!