Counting # of occurances of words in text
 

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?

Counting # of occurances of words in text
 

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


Mike G wrote:

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

Counting # of occurances of words in text
 

=(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)


"Mike G" wrote in message
...
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?

Counting # of occurances of words in text
 

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

"Dave Peterson" wrote:

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


Mike G wrote:

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

Counting # of occurances of words in text
 

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!


"PCLIVE" wrote in message
...
=(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)


"Mike G" wrote in message
...
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?

Counting # of occurances of words in text
 

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

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



--
Regards,

Peo Sjoblom



"Mike G" wrote in message
...
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!


"PCLIVE" wrote in message
...
=(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)


"Mike G" wrote in message
...
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?

Counting # of occurances of words in text
 

Thank you Peo....that works perfectly.


"Peo Sjoblom" wrote in message
...
You can but you need to use another function to sum it

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



--
Regards,

Peo Sjoblom



"Mike G" wrote in message
...
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!


"PCLIVE" wrote in message
...
=(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)


"Mike G" wrote in message
...
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?

Counting # of occurances of words in text
 

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

Is it just the lowercase you didn't like?

Toppers wrote:

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

"Dave Peterson" wrote:

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


Mike G wrote:

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


--

Dave Peterson

Counting # of occurances of words in text
 

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


"Peo Sjoblom" wrote:

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

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



--
Regards,

Peo Sjoblom



"Mike G" wrote in message
...
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!


"PCLIVE" wrote in message
...
=(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)


"Mike G" wrote in message
...
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?

Counting # of occurances of words in text
 

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)

"Teethless mama" wrote:

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


"Peo Sjoblom" wrote:

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

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



--
Regards,

Peo Sjoblom



"Mike G" wrote in message
...
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!


"PCLIVE" wrote in message
...
=(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)


"Mike G" wrote in message
...
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?

Counting # of occurances of words in text
 

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


--
Regards,

Peo Sjoblom


"Teethless mama" wrote in message
...
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


"Peo Sjoblom" wrote:

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

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



--
Regards,

Peo Sjoblom



"Mike G" wrote in message
...
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!


"PCLIVE" wrote in message
...
=(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)


"Mike G" wrote in message
...
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?

Counting # of occurances of words in text
 

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.

"Peo Sjoblom" wrote in message
...
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


--
Regards,

Peo Sjoblom