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