COUNTIF/CONTAINS
Hello,
I am attempting to count how many times a specific word appears in a text entry in a column. I normally achieve this by filtering by "contains" on the actual data sheet. My goal here is to automate this with a formula.
Example: Count how many times the Resolution column contains "duplicate"
I have tried the COUNTIF formula with CONTAINS and always get a 0. I've also tried HAS and FIND and get errors.
Any help is appreciated.
Best Answer
-
I hope you're well and safe!
Try something like this.
=COUNTIFS(Resolution:Resolution, CONTAINS("Duplicate", @cell))
Did that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
I hope you're well and safe!
Try something like this.
=COUNTIFS(Resolution:Resolution, CONTAINS("Duplicate", @cell))
Did that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Andrée Starå That worked! Thank you so much!
Would you be any help with a Does not contain? I added the NOT function to the formula you provided and it returned perfectly. But I need it to exclude multiple items. I get an unparseable if I add more than one word.
=COUNTIFS({Strategic Initiatives Patient Inqu... Range 1}, NOT(CONTAINS("duplicate", @cell))) - this one works
How can I make that work to exclude "duplicate" "coach" "psr: etc.?
-
@Andrée Starå I am struggling with a relative problem. I am just trying to count how many times the word "learning" appears in a column on a referenced sheet (New L&E Project Tracking Template). The column I want to look at is titled "Team." There are some cells where multiple values can live, but if "Learning" shows up at all in a cell I want to count it. I have tried so many different combinations of formulas and keep getting unparseable. What am I doing wrong??
=COUNTIFS({NEW L&E Project Tracking Template Range 2}, CONTAINS("Learning", @{NEW L&E Project Tracking Template Range 2}))
-
Did you ever get help with the Not(Contains) formula? I have the same issue, I need to eliminate a few entries from the same {field}
=COUNTIFS({Ava Lead}, Patroller@row, {Ava Route Route}, <>"AVA IC / Dispatch")
this works for AVA IC / Dispatch but each time I try to add another field SIT Safety I get denied....
-
I am having a similar issue using this formula combo. I currently have a data prep sheet that takes information from a master sheet and some other places so I can make a dashboard. I am trying to use a countifs/contain formula to display the count for how many activities I have for each department. The formula works when there is only one activity for each department but some activities are done through two departments (i.e. two listed in the same cell). My formula works for one department but not if there are more-
COUNTIFS({DifferentSheetRange1}, "Active/Published", {DifferentSheetRange2}, [Column5]@row)
Column5 is the list of departments on the DataPrep sheet and I want it to check the other sheet for items based on that column. If there is more than one department listed in DifferentSheetRange2, then my formula returns 0. I have tried adding in CONTAINS, FIND, HAS but to no avail.
-
My problem was solved
-
Would you care to share how you solved it please?
I have a similar function:
=COUNTIFS({ProjectDepartments}, CONTAINS(Department@row, {ProjectDepartments}))
Where the {ProjectDepartments} is column in a different sheet can have multiple departments input from a dropdown, and the Department@row is the value I am looking for. I think I have something switched around. How did you solve the issue?
I am looking to add to this by {ProjectHealth} = "Green", but can't even get the total times that each department appears in the list of my projects.
Thank you @ka36 if you are able to post the solution.
-
With a multi-select dropdown you'll want to use HAS to see if the cell has that one value selected or not. Try this:
=COUNTIFS({ProjectDepartments}, HAS(@cell, Department@row))
Then you can add in your other criteria:
=COUNTIFS({ProjectDepartments}, HAS(@cell, Department@row), {ProjectHealth}, "Green")
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you so much for the response! I am checking that now and will report back shortly!
-
@Genevieve P. , hello again,
The first formula worked wonderfully for the total number of projects each department is involved in, thank you very much!
Once I fixed my ProjectHealth reference (I named it ScheduleHealth for some reason) it worked like a charm!
Again, thank you so much - my dashboards are starting to look so much better.
-
Glad to hear it! 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. hello! I am referencing this solution and am still having issues. I am looking to source the # of Events with an "Approved" status from our event tracker by pillar. "Event" is a record "Type", but "Type" is a multi-select column.
This is my current formula, which is returning back 0. [Example 1}@row is pillar name.
=COUNTIFS({Pillar}, [Example 1]@row, {Status}, "Approved", {Type}, HAS({Type}, "Event"))
Thanks in advance for your assistance!
-
Hey @Gabrielle G.
You're very close! In the HAS function, instead of listing the entire range again, you'll want to use @cell to say "if the cell has...XXX"
=COUNTIFS({Pillar}, [Example 1]@row, {Status}, "Approved", {Type}, HAS(@cell, "Event"))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. that worked! Thank you :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!