Count the number of times a word appears in a column


Hello everybody!

I need help with a formula that I am not sure how to enter for what I am looking for. I am building a dashboard on certain metrics that align with a key result. For this we have several results, two of them being Establish Success and Success.

I need to know the number of projects that align with Establish Success, that one is easy I have been using the below formula:

=COUNTIF(OKR:OKR, CONTAINS("Establish Success", @cell))

That works, however for the Success one, if I use the one above it will give me a result that includes Establish Success and that won't give me accurate data, so I have been using the one below:

=COUNTIF(OKR:OKR, HAS("Success", @cell))

But I just realized that for projects that meet both key results, I am not getting the data, screenshot below:

The above should show 1 as there is one that says Success, even if the project key result also has Establish Success, I am hoping this makes sense.

I have tried FIND but that doesn't seem to work. I could move the mapping over to columns with the name of key results and use a check mark for tracking, but I am thinking there must be a formula that won't require me to change all the settings of the trackers.

Appreciate any help you can provide.



Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!