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.

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.

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.

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.

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.

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 -

Hey Pete,

I have got an other issue now. Iam actually looking for the term "OS" which
is Operating System. But its also counting whereever OS appears, say my cell
has a term "MICROSOFT" where OS is imbibed. I want the exact term in the
range to be counted.

Please help me out. I tried putting in the "Exact" formulae, but count not
succeed.

Prashanth KR.



"Pete_UK" wrote:

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 -

If you are sure that OS will always appear with a space either side, then
you can enter it as <spaceOS<space in your list of words. However, you
might have a sentence with OS at the end, followed by a full-stop, or it
might be written as " ... OS-Windows XP ... ", so you have to decide whether
<spaceOS or OS<space would be more appropriate.

Hope this helps.

Pete

"Prashanth KR" wrote in message
...

Hey Pete,

I have got an other issue now. Iam actually looking for the term "OS"
which
is Operating System. But its also counting whereever OS appears, say my
cell
has a term "MICROSOFT" where OS is imbibed. I want the exact term in the
range to be counted.

Please help me out. I tried putting in the "Exact" formulae, but count not
succeed.

Prashanth KR.



"Pete_UK" wrote:

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 -

Thanks Pete... thanks for the clue. I shall use my logic to take appropriate
wordings in the list.

Thanks again,
Prashanth KR.



"Pete_UK" wrote:

If you are sure that OS will always appear with a space either side, then
you can enter it as <spaceOS<space in your list of words. However, you
might have a sentence with OS at the end, followed by a full-stop, or it
might be written as " ... OS-Windows XP ... ", so you have to decide whether
<spaceOS or OS<space would be more appropriate.

Hope this helps.

Pete

"Prashanth KR" wrote in message
...

Hey Pete,

I have got an other issue now. Iam actually looking for the term "OS"
which
is Operating System. But its also counting whereever OS appears, say my
cell
has a term "MICROSOFT" where OS is imbibed. I want the exact term in the
range to be counted.

Please help me out. I tried putting in the "Exact" formulae, but count not
succeed.

Prashanth KR.



"Pete_UK" wrote:

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 -

You're welcome - hope we have it sorted now.

Pete

On Mar 4, 10:13*am, Prashanth KR
wrote:
Thanks Pete... thanks for the clue. I shall use my logic to take appropriate
wordings in the list.

Thanks again,
Prashanth KR.



"Pete_UK" wrote:
If you are sure that OS will always appear with a space either side, then
you can enter it as <spaceOS<space in your list of words. However, you
might have a sentence with OS at the end, followed by a full-stop, or it
might be written as " ... OS-Windows XP ... ", so you have to decide whether
<spaceOS or OS<space would be more appropriate.

Hope this helps.

Pete

"Prashanth KR" wrote in message
...

Hey Pete,

I have got an other issue now. Iam actually looking for the term "OS"
which
is Operating System. But its also counting whereever OS appears, say my
cell
has a term "MICROSOFT" where OS is imbibed. I want the exact term in the
range to be counted.

Please help me out. I tried putting in the "Exact" formulae, but count not
succeed.

Prashanth KR.

"Pete_UK" wrote:

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 -- Hide quoted text -

- Show quoted text -