Countif containing specific text
Hello!
I am trying to count to see how often a specific word, in this case a person's name, appears in a range of text. I've looked over similar questions and have started a formula, but I end up with an #INVALID OPERATION error.
My formula is
=COUNTIF({January Range}, FIND("Cassie", @cell, > 0))
I've also tried
=COUNTIF({January Range}, FIND("Cassie", @cell > 0))
Which instead gives me #INVALID DATA TYPE error.
Thank you for any help with this!
Best Answer

It is just a misplaced parenthesis.
Here's the logic...
FIND produces a numerical value. So to compare that to a number (such as greater than zero), you would close the FIND function THEN put in your greater than zero portion. So...
Close the FIND
FIND("Cassie", @cell)
Then make your comparison
FIND("Cassie", @cell) > 0
And now the solution
Then use that as your criteria.
=COUNTIFS({January Range}, FIND("Cassie", @cell) > 0)
Answers

It is just a misplaced parenthesis.
Here's the logic...
FIND produces a numerical value. So to compare that to a number (such as greater than zero), you would close the FIND function THEN put in your greater than zero portion. So...
Close the FIND
FIND("Cassie", @cell)
Then make your comparison
FIND("Cassie", @cell) > 0
And now the solution
Then use that as your criteria.
=COUNTIFS({January Range}, FIND("Cassie", @cell) > 0)

Wow, I can't believe it was something so small. Thank you so much for finding that for me, that worked out!

Happy to help! 👍️

Is it possible to build off this formula to exclude a value if another criteria is met? In my example, I'm trying to calculate the number of tasks assigned to a team member (I have that part down using the formula above), and exclude any tasks that are complete (here's where I'm struggling). In my sheet, I have a column that uses RGYG balls to denote the status, where grey signifies complete. How do I tack on to my original formula so that I can exclude any completed tasks?

If this is your original formula...
=COUNTIFS({Other Sheet Name Range}, FIND("Name", @cell) > 0)
Then the BOLD portion below would be what the additional criteria would follow.
=COUNTIFS({Other Sheet Name Range}, FIND("Name", @cell) > 0, {Other Sheet RYGG Column}, @cell <> "Grey")

Worked like a charm  thank you!

Happy to help! 👍️

OMG Thank you so much that saved my formula!!!!!

I'm having a similar issue. My condition is that if the status column = "Active", I want to find "AWS" under the area column. I get #invalid operation
=COUNTIFS({IT Ops Project Portfolio Range 12}, "Active", FIND("AWS", @cell) > 0)
With this, I get #invalid data type
=COUNTIFS({IT Ops Project Portfolio Range 12}, "Active", FIND({IT Ops Project Portfolio Range 15}, "AWS"))
What am I doing wrong?

@mmoulton Try this:
=COUNTIFS({IT Ops Project Portfolio Range 12}, "Active", {IT Ops Project Portfolio Range 15}, FIND("AWS", @cell) > 0)

In the original example, the user searches for specific text ("Cassie"). Is there a way to replace that with a reference? For example, what if I wanted to COUNTIF the Program@row appeared in a text string somewhere else? Is that possible? We have hundreds of these and they change with some regularity, so it's impractical to enter the actual numbers into the formula.

@Greg DiNardo You would replace "Specific Text" with Program@row.

Hi Paul! Thanks  that knows to check if the text string includes the value in that cell rather than matches it exactly?


How do I adjust the FIND function to find an exact match within those parentheses? For example, in the FIND("Services",@cell)>0, I have cells that contain "Managed Services" and "Cloud Services" as well as just "Services". How do I adjust the FIND portion of my formula to only render a count for only "Services" alone (not that includes Managed Services or Cloud Services) and not include anything else in the count?
Thanks in advance!
Help Article Resources
Categories
Check out the Formula Handbook template!