Counting specific word occurences in a cell

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

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

--
HTH

Bob Phillips

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

"KenRamoska" <KenRamoska@discussions.microsoft.com> wrote in message
news:10DC1738-4E45-4933-804B-EE671DD3FD4C@microsoft.com...
> 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

Originally Posted by 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

Try:

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

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

Scott

Try this:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"KenRamoska" <KenRamoska@discussions.microsoft.com> wrote in message
news:10DC1738-4E45-4933-804B-EE671DD3FD4C@microsoft.com...
> 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