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),UPPER(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" <(E-Mail Removed)> wrote in message
news:422B8103-D581-4274-A639-(E-Mail Removed)...
> 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),UPPER(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" <(E-Mail Removed)> wrote in message
> news:422B8103-D581-4274-A639-(E-Mail Removed)...
> > 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),UPPER(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" <(E-Mail Removed)> wrote in message
> news:422B8103-D581-4274-A639-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:E29F975E-6678-4919-98F3-(E-Mail Removed)...
> 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),UPPER(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" <(E-Mail Removed)> wrote in message
>> news:422B8103-D581-4274-A639-(E-Mail Removed)...
>> > 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"&LEN(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")

"(E-Mail Removed)" 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")
>
> "(E-Mail Removed)" 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.
> >
> >
> >