Count if all, cells contain a specific word.
Hello,
I am still new to using formulas and I am hoping someone can help with finishing this one. I have an information sheet and a metrics sheet to build a dashboard off.
On the dashboard metric sheet, I am wanting to count the cells in a column ion the data sheet that contain the text "PPM" the column I am referring to is a dropdown and allows multi. Currently I have
=COUNTIF({Range 1}, [Column1]@row)
(Row cell contains the word I am searching for.)
That currently returns count for cells that only contain the @cell, not cells with multi values within them.
What is the extra part to the formula I would need? please help?
Thank you
Answers
-
Hey @Britt E
Try this formula: =COUNTIF({Range 1}, CONTAINS("PPM", @cell))
It worked fine for me but let me know if I missed anything.
Itai Perez
Reporting and Project Manager
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
-
Hi @Britt E,
For this use a cross sheet reference to your dropdown column only and a formula like this:
=COUNTIF({Dropdown}, CONTAINS("PPM", @cell))
Hope this helps, but if you've any problems/questions then just ask! 🙂
-
Thank you both @Nick Korna and @Itai , that works well.
What could I do if I want the count to be dependant on cell value that is in another column that is just free text determined by a formula?
-
You'd change the formula to COUNTIFS and add in the extra column(s) & criteria. That the other column's value is calculated rather than directly input shouldn't matter.
If you're looking for a single text value, then you can just use "text" as the criteria, or use CONTAINS in a similar way.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 352 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!