Counting specific word occurences in a cell

K

KenRamoska

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
 
B

Bob Phillips

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

--
HTH

Bob Phillips

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

Maistrye

KenRamoska said:
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
Click to expand...

Try:

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

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

Scott
 
Top