COUNTIFS not resulting in same count as filter

Erin G

Hi all,

Strange situation over here...I am looking to count the number of cells with the word "waste" in a column called Narrative. I started just by filtering the data to understand what volumes I would get.

Then I decided to use a formula to pull this data onto another sheet:

=COUNTIF({2021 Q3 Narrative}, FIND("waste", @cell) > 0)

The formula is counting, but not enough. It gives a result of 169, but my filter finds 173.

My great appreciation to this community, it was previous examples that helped me build the FIND formula. So thanks in advance for any thoughts on this one!

Erin

Kelly Drake

Erin - A hint at the answer is that your filter is set to contains....

What is the column type you're using in the 2021 Q3 Narrative column? Is it a multi-select dropdown or is it a text/numbers column and your difference can be accounted by 4 cells having something in addition to "waste"?

CONTAINS may be better formulas for you to use - more info and details on the syntax here: https://help.smartsheet.com/function/contains

Note: HAS may create the same discrepancy as FIND becuase it looks for exact matches so cells with anything additonal would not be counted.