COUNTIF/CONTAINS

chelsi.johnson

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.

Andrée Starå

Hi @chelsi.johnson

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!

Andrée Starå

Hi @chelsi.johnson

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!

chelsi.johnson

@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.?

np68767

@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}))

SkiPatrolScott

@Andrée Starå @chelsi.johnson

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....

ka36

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.

ka36

https://community.smartsheet.com/discussion/comment/364003#Comment_364003

My problem was solved

Bill in Ohio

https://community.smartsheet.com/discussion/comment/364143#Comment_364143

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.

Genevieve P.

Hi @Bill in Ohio

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

Bill in Ohio

Thank you so much for the response! I am checking that now and will report back shortly!

Bill in Ohio

@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.

Genevieve P.

Glad to hear it! 🙂

Gabrielle G.

@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!

Genevieve P.

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

Gabrielle G.

@Genevieve P. that worked! Thank you :)