Contains instead of Equal

[Guest]

Is there a function that counts a cell if it contains a value rather than
equals that value?

For example, I'd like to count the number of cells that contain the word
"dog".

First cell is (dog, cat, bird)
Second cell is (dog)
Third cell is (cat, bird)
Forth cell is (dog, cat)

The formula would then return a value of 3, because the word "dog" shows up
in three cells.

Is this possible? If so, does anyone have any suggestions? Thanks in
advance.

 

[Bob Phillips]

=SUMPRODUCT(--(ISNUMBER(FIND("cat",G2:G100))))

if case-sensitive

=SUMPRODUCT(--(ISNUMBER(SEARCH("cat",G2:G100))))

if not

 

[Domenic]

Try...

=COUNTIF(A1:A100,"*dog*")

or

=COUNTIF(A:A,"*dog*")

or

=COUNTIF(A1:A100,"*"&B1&"*")

....where B1 contains your criterion, such as 'dog'.

Hope this helps!

 

[Guest]

try
=sum(if(iserror(find("dog",{range})),0,1))
entered as an array equation (control-Shift-Enter

 

[Roger Govier]

Hi Jennifer

One way
{=SUM(--ISNUMBER(FIND("dog",A1:C20)))}

Note this is an array formula so commit with Ctrl+Shift+Enter not just Enter
either when entering or amending.
Do not type the curly braces { } , Excel will enter these for you.
Change range to suit.

Regards

Roger Govier