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!.

 

[Steve Jackson]

I have used the =COUNTIF(range,A1) formula and it works great. I then
copied this for B1, C1, D1 etc etc for occurances of different words
for the same range of cells. I then want to try more with the
spreadsheet I have and hit another stumbling block. Basically what I
am trying to do is this:
1. Produce a spreadsheet of football results, players played, goals
scored, red/yellow cards, man of the match.
2. From this spreadsheet set up functions that will show statistics
for that team. It should be made easy so that all I have to do is
input the players who played, who scored etc etc, and then the
functions would tally up the statistics automatically.

So far I have created the spreadsheet like this:

Columns A-D - Game information (date, venue, opposition, result)
Column E - Player 1 name
Column F - Player 1 Goals scored
Colmun G - Player 1 Man of the Match
Column H - Player 1 rating

Column E-H are then repeats of columns E-F but for player 2 and so on.

It is straight forward with the formula =COUNTIF(range,A1) to highligh
appearances because it is the pure occurance of a word. The problem I
get is when I am trying to count goals scored, MOM and player rating
for each specific player:

I need a formula that will find the occurance of a word (the player's
name) and then for that word (player); add up all the MOMs, goals
scored and player ratings for that player. Because the colums are set
out in a consistent way the formula will only need to reference 1, 2
or 3 cells to the right of the word occurance. Someway the formula
needs to tell Excel to follow this pattern:
1. Find a specifc word (this word is referenced in a cell as per the
orginal COUNTIF formula)
2. Look 1 cell to the right of that word and add whatever is in that
cell
3. Keep counting for each occurance of that word.

A modification of that formula would be to look 2 cells to the right
and then 3 cells to the right.

I understand this is a big ask but any help will be very gratefully
received.