countif function
 

can i count how often a word occurs in a cell that has more than one word in
the cell???

countif function
 

Multiple time in just one cell?

=(LEN(A1)-LEN(SUBSTITUTE(A1,"the_word","")))/LEN("the_word")

or once per cell in many cells?

=COUNTIF(A2:A20,"*the_word*")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nwilson" wrote in message
...
can i count how often a word occurs in a cell that has more than one word
in
the cell???

countif function
 

For one cell try this:
A1: Sugar
D1: Sugar Plums contain sugar

B1: =(LEN(D1)-LEN(SUBSTITUTE(UPPER(D1),UPPER(A1),"")))/LEN(A1)
returns 2

For multiple cells:
A1: Sugar
D1: Sugar Plums contain sugar
D2: Sugar and Spice
D3: Sugar Bears contain no sugar
D4: Candy Apples contain sugar
D5: Sugar

C1:
=SUMPRODUCT(LEN(D1:D10)-LEN(SUBSTITUTE(UPPER(D1:D10),UPPER(A1),"")))/LEN(A1)
returns 7

Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"nwilson" wrote:

can i count how often a word occurs in a cell that has more than one word in
the cell???