countif function

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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(D110)-LEN(SUBSTITUTE(UPPER(D110),UPPER(A1),"")))/LEN(A1)
returns 7

Does that help?

••••••••••
Regards,
Ron