Countif containing specific text

Daryl Lee

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!

Paul Newcome

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)

Paul Newcome

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)

Daryl Lee

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

Paul Newcome

Happy to help! 👍️

lvolpe

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?

Paul Newcome

@lvolpe

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

lvolpe

Worked like a charm - thank you!

Paul Newcome

Happy to help! 👍️

dschacter

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

mmoulton

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?

Paul Newcome

@mmoulton Try this:

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

Greg DiNardo

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.

Paul Newcome

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

Greg DiNardo

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

Paul Newcome

@Greg DiNardo That is correct.

StaceyFC

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!