Counting # of occurances of words in text

[Mike G]

If I have a cell that contains "good things come in good packages" and want
to count the number of occurances of the word "good" how would I do that?
Have tried countif and sumproduct and both give me #name?

 

[Dave Peterson]

=(len(a1)-len(substitute(a1,"good","")))/len("good")

 

[PCLIVE]

=(LEN(A1)-LEN(SUBSTITUTE(A1,"good","")))/4

If the word to serch is in a particular cell, say B1, then:
=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1)

 

[Guest]

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

 

[Mike G]

Thanks for the formulas.....If I wanted to check more that one cell i.e.
a1:a25 I thought I could replace the A1 with A1:A25, I get the #value
again... Thanks for your patience!

 

[Peo Sjoblom]

You can but you need to use another function to sum it

=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"good","")))/4)

 

[Mike G]

Thank you Peo....that works perfectly.

You can but you need to use another function to sum it

=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"good","")))/4)

 

[Dave Peterson]

I don't see a difference. (But I'm getting old(er)!)

Is it just the lowercase you didn't like?

 

[Guest]

Your formula only work for "good", if OP has "GOOD", or "Good" then your
formula will not work. The formula below will take care all that.

=SUMPRODUCT(--(LEN(A1:A100)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A100,"good",""),"Good",""),"GOOD",""))))/4

 

[Guest]

I always thought substitute was case insensitive - but I get the same
results. You could shorten it to:
=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),"good","")))/4)

 

[Peo Sjoblom]

It wasn't my formula, just added the sumproduct to it since the OP tried to
use it on a range, I was too lazy to add anything else. Btw, I use either
UPPER or LOWER which is better than multiple SUBSTITUTE

Nor would I use /4, I would use a cell reference like B2 then /LEN(B2)
B2 would obviously replace "Good" wherever it occurs

=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),LOWER(B2),"")))/LEN(B2))

that would take care of typos like gooD as well

 

[Mike G]

Wow...this is a good education. I have since made only one more change and
that is to add "$" to the range. This will allow me to copy the formula
down, keeping the same range and change "good" to another word (b3) I would
be searaching for. . What I am doing is to count keywords on customer
responses . Hoping to get more "good's" than "bad's" :>) Thanks again.