Count the occurances of a specific word

[Steve Jackson]

I have been trying to create a formula that will count the number of
times a specific word occurs in a block of cells. The functions I have
looked at in Excel seem to just use numbers and do not allow text
instead.

Is there a way of doing this? If so, which function do I use?

 

[JE McGimpsey]

one way:

=COUNTIF(rng, "SpecificWord")

 

[Bernie Deitrick]

Steve,

=COUNTIF(A1:C10,"*specific word*")

or to only count where the whole cell has just the word

=COUNTIF(A1:C10,"specific word")

HTH,
Bernie
MS Excel MVP

 

[Alan]

Say you were looking for the word 'Cat' in the range A150,
=COUNTIF(A150,"Cat")
This is assuming that the cells only contain one word (or value). If you are
trying to find 'Cat' within a text string like "The black cat" in the cells
then this wont work. Post back if this is the case,
Regards,

 

[Bob Phillips]

If the word occupies the cell on itself

=COUNTIF(A1:A100,"word")

If it could be contained in the cell then

=SUMPRODUCT(--(ISNUMBER(SEARCH("word",A1:A100))))

 

[DME]

Is there a way to reference a cell. For instance, I have "cat" in cell A1.
Is there a way to COUNTIF(range,=a1) So the formual counts the number of
times "Cat" appears in A!.

 

[DME]

I think I may have confused everyone after I read the post.

To make it a little easier, I want to replace "word" with a cell reference.
ie. A1. SO what the formula does is looks for the word in a specific cell
and the counts the occurences in the range.

Thanks for the help!

 

[JE McGimpsey]

One way:

=COUNTIF(range,A1)

Is there a way to reference a cell. For instance, I have "cat" in cell A1.
Is there a way to COUNTIF(range,=a1) So the formual counts the number of
times "Cat" appears in A!.