Thanks for feeding back again.

XXXX will be okay as long as you do not have text about Australian
Lager!! Maybe better to make it XXXXXXXXXXXX.

Pete

On Mar 3, 2:45*pm, Prashanth KR
wrote:
Thanks again Pete..... I was actually getting the error. But tried my own
logic by putting in "1" in the blank cells. But as U suggested it makes more
sense to update "XXXX" since their are probabilities of '1' appearing in the
searching cell.

Thanks again,
Prashanth KR.



"Pete_UK" wrote:
You're welcome - glad to be of help.

I forgot to point out that you can always put "xxx" or "zzz" or some-such in
the unused cells of the C1:C10 range, to avoid errors from the formula
caused by blank cells. Then you can have a much larger range defined in the
formula and not have to amend it very often.

Pete

"Prashanth KR" wrote in message
...

Hi Pete,

Thank you very much. I appreciate your prompt reply and taking care of my
problem.

It really serves me a great deal. Thanks much again.

Prashanth KR.

"Pete_UK" wrote:

This seems to do it:

=SUMPRODUCT(((LEN(A1)-(LEN(SUBSTITUTE(UPPER(A1),UPPER(C1:C3),""))))/
LEN(C1:C3)))

I only tested it with three words, but make the range C1:C10 (twice)
if you have 10 words. Will return an error if any of the cells in the
range are empty.

Hope this helps.

Pete

On Feb 29, 9:22 am, Prashanth KR
wrote:
Hi,

I have a specific problem. Your timely help will be greatly
appreciated.

I have a text in a form of paragraph in A1 (eg., The BlackBerry e-mail
system is still unmatched. I set the service up with Gmail, Microsoft
Exchange Web Access, and *Yahoo! Mail accounts within minutes. The
e-mail
system also supports attachments, displaying picture attachments, and
PowerPoints, but it boils PDFs and other Microsoft Office documents
down to
text).

I have a range of words say in C1:C10 (having Blackberry,
Microsoft..... etc)

I want to count the total no. of occurances of the words cited in
C1:C10.
Currently Iam able to count only one such instance by using the below
mentioned formulae:
=((LEN(A1)-(LEN(SUBSTITUTE(A1,C1,""))))/LEN(C1)) where C1 contains the
term
"Blackberry". Iam not able to substitute C1 with the range C1:C10.

Also it does not count if the sentence has a term which is
case-sensitive.

Kindly help.
Prashanth KR.- Hide quoted text -

- Show quoted text -