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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.9K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.8K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!