COUNTIFS not resulting in same count as filter
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
Best Answer

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 multiselect 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 (she/her/hers)
STARBUCKS COFFEE COMPANY business optimization product manager
Answers

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 multiselect 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 (she/her/hers)
STARBUCKS COFFEE COMPANY business optimization product manager

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
Help Article Resources
Categories
Check out the Formula Handbook template!