Can someone devise a formula to sum-up the number of times a character or
piece of string repeats in a range of cells?

For example:
[A1: When]
[A2: How]
[A3: Why]

When you apply such a formula on the range [A1:A3] for the character €œW€� it
gives you a reply of 3 (A1, A2 & A3) and when for €œWH€� it returns €œ2€� (A1 &
A3).

Thanx in advance & Best Regards

If your range is fixed like you show, and if the text you want to find is in
B1, then you can use this...

=IF(B1<"",(LEN(UPPER(A1&CHAR(1)&A2&CHAR(1)&A3))-LEN(SUBSTITUTE(UPPER(A1&CHAR(1)&A2&CHAR(1)&A3),UPP ER(B1),"")))/LEN(B1),"")

Simply extend the concatenations to cover the maximum range you will have.
The CHAR(1) in-between the cell addresses are to make sure no false
positives are found (using "wh" for example, to make sure a cell doesn't end
with "w" and the next cell begin with "h"). We could have used a normal
character in place of the CHAR(1), but using CHAR(1) protects against that
character being in the "find" text.

Rick


"FARAZ QURESHI" wrote in message
...
Can someone devise a formula to sum-up the number of times a character or
piece of string repeats in a range of cells?

For example:
[A1: When]
[A2: How]
[A3: Why]

When you apply such a formula on the range [A1:A3] for the character €œW€�
it
gives you a reply of 3 (A1, A2 & A3) and when for €œWH€� it returns €œ2€� (A1
&
A3).

Thanx in advance & Best Regards

YAHOOOOOOOOO!
Thanx Rick!!!
U R Great!!!!!

"Rick Rothstein (MVP - VB)" wrote:

If your range is fixed like you show, and if the text you want to find is in
B1, then you can use this...

=IF(B1<"",(LEN(UPPER(A1&CHAR(1)&A2&CHAR(1)&A3))-LEN(SUBSTITUTE(UPPER(A1&CHAR(1)&A2&CHAR(1)&A3),UPP ER(B1),"")))/LEN(B1),"")

Simply extend the concatenations to cover the maximum range you will have.
The CHAR(1) in-between the cell addresses are to make sure no false
positives are found (using "wh" for example, to make sure a cell doesn't end
with "w" and the next cell begin with "h"). We could have used a normal
character in place of the CHAR(1), but using CHAR(1) protects against that
character being in the "find" text.

Rick


"FARAZ QURESHI" wrote in message
...
Can someone devise a formula to sum-up the number of times a character or
piece of string repeats in a range of cells?

For example:
[A1: When]
[A2: How]
[A3: Why]

When you apply such a formula on the range [A1:A3] for the character €œW€�
it
gives you a reply of 3 (A1, A2 & A3) and when for €œWH€� it returns €œ2€� (A1
&
A3).

Thanx in advance & Best Regards

By the way Rick! If there isn't any repetition of same character in one cell.
Any idea of using some sort of sumproduct() funtion like:

=Sumproduct(--(find("W",A1:A3)0))?

Concatenations of a large range otherwise would take too much length and
formula would not be possible.

The strategy presented former however I do still accept was outstanding and
truly a GREAT one!

"Rick Rothstein (MVP - VB)" wrote:

If your range is fixed like you show, and if the text you want to find is in
B1, then you can use this...

=IF(B1<"",(LEN(UPPER(A1&CHAR(1)&A2&CHAR(1)&A3))-LEN(SUBSTITUTE(UPPER(A1&CHAR(1)&A2&CHAR(1)&A3),UPP ER(B1),"")))/LEN(B1),"")

Simply extend the concatenations to cover the maximum range you will have.
The CHAR(1) in-between the cell addresses are to make sure no false
positives are found (using "wh" for example, to make sure a cell doesn't end
with "w" and the next cell begin with "h"). We could have used a normal
character in place of the CHAR(1), but using CHAR(1) protects against that
character being in the "find" text.

Rick


"FARAZ QURESHI" wrote in message
...
Can someone devise a formula to sum-up the number of times a character or
piece of string repeats in a range of cells?

For example:
[A1: When]
[A2: How]
[A3: Why]

When you apply such a formula on the range [A1:A3] for the character €œW€�
it
gives you a reply of 3 (A1, A2 & A3) and when for €œWH€� it returns €œ2€� (A1
&
A3).

Thanx in advance & Best Regards

Dear Rick,

I am just being nosy and this thread is of interest to me. I was wondering
if the data extended to say 100 rows, is it possible to count the specific
characters.

Thanks.

If you have a large range, and if there could be repeats within a cell, you
could do this instead. Assuming your text phrases are in column A and B1
holds the text you want to search for, put this formula

=SUMPRODUCT(--ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("A$1:A"&LEN(A1 ))),LEN($B$1)),$B$1)))

in a blank column and copy it down. What the formula does is list the number
of times the text in B1 appears in each cell in column A; so, to get your
total, just SUM up the column you placed the above formulas in. If you don't
want to show these individual counts, hide the column and put your SUM
formula in a different column

Rick


"FARAZ QURESHI" wrote in message
...
By the way Rick! If there isn't any repetition of same character in one
cell.
Any idea of using some sort of sumproduct() funtion like:

=Sumproduct(--(find("W",A1:A3)0))?

Concatenations of a large range otherwise would take too much length and
formula would not be possible.

The strategy presented former however I do still accept was outstanding
and
truly a GREAT one!

"Rick Rothstein (MVP - VB)" wrote:

If your range is fixed like you show, and if the text you want to find is
in
B1, then you can use this...

=IF(B1<"",(LEN(UPPER(A1&CHAR(1)&A2&CHAR(1)&A3))-LEN(SUBSTITUTE(UPPER(A1&CHAR(1)&A2&CHAR(1)&A3),UPP ER(B1),"")))/LEN(B1),"")

Simply extend the concatenations to cover the maximum range you will
have.
The CHAR(1) in-between the cell addresses are to make sure no false
positives are found (using "wh" for example, to make sure a cell doesn't
end
with "w" and the next cell begin with "h"). We could have used a normal
character in place of the CHAR(1), but using CHAR(1) protects against
that
character being in the "find" text.

Rick


"FARAZ QURESHI" wrote in message
...
Can someone devise a formula to sum-up the number of times a character
or
piece of string repeats in a range of cells?

For example:
[A1: When]
[A2: How]
[A3: Why]

When you apply such a formula on the range [A1:A3] for the character
€œW€�
it
gives you a reply of 3 (A1, A2 & A3) and when for €œWH€� it returns €œ2€�
(A1
&
A3).

Thanx in advance & Best Regards

If you have a large range, and if there could be repeats within a cell,
you could do this instead. Assuming your text phrases are in column A and
B1 holds the text you want to search for, put this formula

=SUMPRODUCT(--ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("A$1:A"&LEN(A1 ))),LEN($B$1)),$B$1)))

in a blank column and copy it down. What the formula does is list the
number of times the text in B1 appears in each cell in column A; so, to
get your total, just SUM up the column you placed the above formulas in.
If you don't want to show these individual counts, hide the column and put
your SUM formula in a different column.

Never mind... don't use this formula, it fails under certain circumstances.

Rick

If you have a large range, and if there could be repeats within a cell,
you could do this instead. Assuming your text phrases are in column A and
B1 holds the text you want to search for, put this formula

=SUMPRODUCT(--ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("A$1:A"&LEN(A1 ))),LEN($B$1)),$B$1)))

in a blank column and copy it down. What the formula does is list the
number of times the text in B1 appears in each cell in column A; so, to
get your total, just SUM up the column you placed the above formulas in.
If you don't want to show these individual counts, hide the column and
put your SUM formula in a different column.

Never mind... don't use this formula, it fails under certain
circumstances.

This formula works...

=SUMPRODUCT(--ISNUMBER(SEARCH(MID(A1&"|",ROW(INDIRECT("A$1:A"&LE N(A1))),LEN($B$1)),$B$1)))

Any character concatenated on the end of the text in the cell being searched
(I used the vertical bar), as long as that character will never appear in
your text, stops the miscount. So, use the above formula, copy it down and
SUM the result as I described in my second post.

Rick

Hey Guys!

CHIP PEARSON has justhelped me in an xclent development in this regard i.e.:

For single character
=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(UPPER(A1:A3),UPPER("w"),"")))

& for string:
=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(UPPER(A1:A3),UPPER("wh"),"")))/LEN("wh")

" wrote:

Dear Rick,

I am just being nosy and this thread is of interest to me. I was wondering
if the data extended to say 100 rows, is it possible to count the specific
characters.

Thanks.

You can also use

=SUMPRODUCT(--(LEN(A1:A3)LEN(SUBSTITUTE(UPPER(A1:A3),"W",""))))

"FARAZ QURESHI" wrote:

Hey Guys!

CHIP PEARSON has justhelped me in an xclent development in this regard i.e.:

For single character
=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(UPPER(A1:A3),UPPER("w"),"")))

& for string:
=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(UPPER(A1:A3),UPPER("wh"),"")))/LEN("wh")

" wrote:

Dear Rick,

I am just being nosy and this thread is of interest to me. I was wondering
if the data extended to say 100 rows, is it possible to count the specific
characters.

Thanks.

Thanx Duke,

But your recommendation in my opinion won't workout for multiple "w" in a
single cell!

"Duke Carey" wrote:

You can also use

=SUMPRODUCT(--(LEN(A1:A3)LEN(SUBSTITUTE(UPPER(A1:A3),"W",""))))

"FARAZ QURESHI" wrote:

Hey Guys!

CHIP PEARSON has justhelped me in an xclent development in this regard i.e.:

For single character
=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(UPPER(A1:A3),UPPER("w"),"")))

& for string:
=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(UPPER(A1:A3),UPPER("wh"),"")))/LEN("wh")

" wrote:

Dear Rick,

I am just being nosy and this thread is of interest to me. I was wondering
if the data extended to say 100 rows, is it possible to count the specific
characters.

Thanks.