Counting specific word occurences in a cell

[Guest]

Hi,
I have some data people put into cells.

For example I had 500 responses. I want to know how many cells have the word
SHRINK in them. Not all 500 people had trouble with Shrink. So in their
paragraph that they typed in the cell they may have mentioned shrink and I
would like to count it.
I tried =countif(A1:A500,"SHRINK") but that didn't work. Is there a way
that Excel can look at a number of words in a cell and just pick out the one.
Shrink is never mentioned more than once in a cell but there are other words
in that cell.

thanks

 

[Bob Phillips]

=COUNTIF(A1:A500,"*Shrink*")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Ragdyer]

Try this:

=COUNTIF(A1:A500,"*shrink*")

 

[Maistrye]

Hi,
I have some data people put into cells.

For example I had 500 responses. I want to know how many cells have the
word
SHRINK in them. Not all 500 people had trouble with Shrink. So in
their
paragraph that they typed in the cell they may have mentioned shrink
and I
would like to count it.
I tried =countif(A1:A500,"SHRINK") but that didn't work. Is there a
way
that Excel can look at a number of words in a cell and just pick out
the one.
Shrink is never mentioned more than once in a cell but there are other
words
in that cell.

thanks

Try:

Case Sensitive:
=SUMPRODUCT(--NOT(ISERROR(FIND("Shrink",A1:A500,1))))

Case Insensitive:
=SUMPRODUCT(--NOT(ISERROR(SEARCH("Shrink",A1:A500,1))))

Scott