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.

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.

Erin G

Hi Kelly,

The Narrative column is Text/Number and all of the cells contain at least a sentence or two.

Sure enough, the CONTAINS equation did it and I now have matching counts. So problem solved :) The final formula for anyone who might come across this later is

=COUNTIF({2021 Q3 Narrative}, CONTAINS("waste", @cell))

Also for edification/posterity - I have been scouring the cells for what might be different and prompting the different count. For example, some have capitalized Waste and some not, some are "wasted" and some are "waste"....but so far I'm not finding anything that accounts for the exact number of missing results. What is it about FIND that makes it not find some text that is found by CONTAINS?

Thank you so much for your help!

Erin