Counting occurances of a string anywhere in a cell, not exact matches.

Posted by David Megnin on July 09, 2001 3:08 PM

Please help:

Column A contains a variety of text, e.g.:

A
Apple
Pineapple
My Apple 1.1
beans
egg
apple pie
red apple
ball

I need to count to occurances of "apple" in the column regardless of it's form. I need the above list to yeald an answer of "5".

Using CountIF I have only been able to count exact matches. I tried combining Search with CountIf, but didn't get anywhere with that.

Thank you very much for any advice or help.
David Megnin

Posted by Mark W. on July 09, 2001 3:15 PM

=COUNTIF(A1:A9,"*apple*")

Posted by David Megnin on July 10, 2001 10:53 AM

Ahhhh, THANK YOU so much, Mark!

Posted by David Megnin on July 10, 2001 11:33 AM

Then counting occurances of text in second column for each counted in first column

The next thing I need to count is occurances of text in a second column:

A B
1 Apple red looking
2 Pineapple Fred
3 My Apple 1.1 sort of redish
4 beans red
5 egg red
6 apple pie green
7 red apple large
8 ball red like an apple

The count should yeald "3" for "apple" and "red"
in columns A and B respectively.

I tried the formula below, but it only resulted in an error.
=COUNTIF(and($A$1:$A$9,"*apple*"),($B$1:$B$9,"*red*"))

Thanks once again for any help.

David

Posted by David Megnin on July 10, 2001 11:36 AM

Then counting occurances of text in second column for each counted in first column

....sorry, my last post had all the spaces between columns A and B stripped from it making it impossible to read... here it is again with periods instead of spaces separating columns.
-----------------------------------------------
The next thing I need to count is occurances of text in a second column:

A B
Apple.............red looking
Pineapple.........Fred
My Apple 1.1......sort of redish
beans.............red
egg...............red
apple pie.........green
red apple.........large
ball..............red like an apple

The count should yeald "3" for "apple" and "red"
in columns A and B respectively.

I tried the formula below, but it only resulted in an error.
=COUNTIF(and($A$1:$A$9,"*apple*"),($B$1:$B$9,"*red*"))

Thanks once again for any help.

David

Posted by Mark W. on July 10, 2001 12:07 PM

Re: Then counting occurances of text in second column for each counted in first column

Need to use an array formula...

{=SUM(ISNUMBER(SEARCH("apple",A1:A8)*SEARCH("red",B1:B8))+0)}

Note: Array formulas are entered using the
Control+Shift+Enter key combination. The
braces, {}, are not type by you. They're
supplied by Excel in recognition that you've
entered an array formula.

Posted by David Megnin on July 10, 2001 12:33 PM

Re: Then counting occurances of text in second column for each counted in first column

Mark, you're a genius! Thanks, it works perfectly.