Google Sheets - COUNTIF (complex)

Here is tough one

I have a Conditional Format via Formula

Please Login or Register  to view this content.

Capture1.PNG

How it works - when I type if B1 any phrase "Bed Covers"
below gets highlighted with some or partial of the text

Capture.PNG

Now what I wanted to do is do a COUNTIF on the Range using the above code.

1. =COUNTIF(A5:A30,NOT(ISERROR(FIND(LOWER(A4),LOWER($B$1)))))

This returns 0

Perhaps because it's like an array?

GOAL is to get a count of what is highlighted using the same formula

Thank you

Hi ALAN - I did attempt to add a link and got the error: "You are not allowed to post any kinds of links, images or videos until you post a few times."
So when I deleted the line - it went through. I should have kept the "Cross Post" reference but that was removed with the URL

Is this site the same as Mr. Excel? If it is the case I might just rather use this one.

Originally Posted by RLONG98

. . . =NOT(ISERROR(FIND(LOWER(A4);LOWER($B$1)))) . . .

Why not just use case-insensitive functions? If A4 doesn't contain ~, * or ? and doesn't begin with a comparison operator like =, you could reduce this to

=COUNTIF($B$1;"*"&A4&"*")

Even if you had to use FIND because of wildcards or comparison operators at the beginning of A4,

=COUNT(FIND(LOWER(A4);LOWER($B$1))

. . . Now what I wanted to do is do a COUNTIF on the Range using the above code.

=COUNTIF(A5:A30,NOT(ISERROR(FIND(LOWER(A4),LOWER($B$1)))))

This returns 0 . . .

The last formula returns 0 because COUNTIF's 2nd argument can be either TRUE or FALSE, nothing else. Since neither of those values appear in A5:A30, COUNTIF correctly returns 0.

Do you want to count the number of cells in A5:A30 which contain something? If so, what would they contain?