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 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 (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 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 (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
- 62.5K Get Help
- 367 Global Discussions
- 202 Industry Talk
- 432 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 447 Show & Tell
- 29 Member Spotlight
- 1 SmartStories
- 285 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!