# Countif containing specific text

Options
✭✭✭

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!

Tags:

«1

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

• ✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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")

• ✭✭
Options

Worked like a charm - thank you!

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

• Options

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

• ✭✭
Options

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?

• ✭✭✭✭✭✭
Options

@mmoulton Try this:

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

• Options

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.

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options
• ✭✭
edited 03/21/23
Options

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?