Contains instead of Equal
 

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.

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

if case-sensitive

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

if not

--
HTH

Bob Phillips

"Jennifer" wrote in message
...
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.

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!

In article ,
"Jennifer" wrote:

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.

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

"Jennifer" wrote:

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.

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


Jennifer wrote:
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.