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?
|