Count all €œZ€� in a Range
 

I know this function will work on a specific cell:
=LEN(A1)-LEN(SUBSTITUTE(A1,"Z",""))

What about counting all letters Z in range A1:A10?
Seems simple. I tried a few things; nothing has worked yet...

Thanks,
Ryan--



--
RyGuy

Count all "Z" in a Range
 

From: http://support.microsoft.com/kb/213889

Formula to Count the Number of Occurrences of a Single Character in a
Range

=SUM(LEN(range)-LEN(SUBSTITUTE(range,"a","")))
where range is the cell range in question, and "a" is replaced by the
character you want to count.

Note The above formula must be entered as an array formula. To enter a
formula as an array formula in Excel, press CTRL+SHIFT+ENTER.

(note this will only find "Z", not "z")


HTH,
JP

On Jan 8, 2:58*pm, ryguy7272
wrote:
I know this function will work on a specific cell:
=LEN(A1)-LEN(SUBSTITUTE(A1,"Z",""))

What about counting all letters Z in range A1:A10?
Seems simple. *I tried a few things; nothing has worked yet...

Thanks,
Ryan--

--
RyGuy

Count all "Z" in a Range
 

Very elegant!!

Regards,
Ryan---


--
RyGuy


"JP" wrote:

From: http://support.microsoft.com/kb/213889

Formula to Count the Number of Occurrences of a Single Character in a
Range

=SUM(LEN(range)-LEN(SUBSTITUTE(range,"a","")))
where range is the cell range in question, and "a" is replaced by the
character you want to count.

Note The above formula must be entered as an array formula. To enter a
formula as an array formula in Excel, press CTRL+SHIFT+ENTER.

(note this will only find "Z", not "z")


HTH,
JP

On Jan 8, 2:58 pm, ryguy7272
wrote:
I know this function will work on a specific cell:
=LEN(A1)-LEN(SUBSTITUTE(A1,"Z",""))

What about counting all letters Z in range A1:A10?
Seems simple. I tried a few things; nothing has worked yet...

Thanks,
Ryan--

--
RyGuy

Count all "Z" in a Range
 

You could use:

=SUMproduct(LEN(range)-LEN(SUBSTITUTE(range,"a","")))

and not have to array enter the formula.

JP wrote:

From: http://support.microsoft.com/kb/213889

Formula to Count the Number of Occurrences of a Single Character in a
Range

=SUM(LEN(range)-LEN(SUBSTITUTE(range,"a","")))
where range is the cell range in question, and "a" is replaced by the
character you want to count.

Note The above formula must be entered as an array formula. To enter a
formula as an array formula in Excel, press CTRL+SHIFT+ENTER.

(note this will only find "Z", not "z")

HTH,
JP

On Jan 8, 2:58 pm, ryguy7272
wrote:
I know this function will work on a specific cell:
=LEN(A1)-LEN(SUBSTITUTE(A1,"Z",""))

What about counting all letters Z in range A1:A10?
Seems simple. I tried a few things; nothing has worked yet...

Thanks,
Ryan--

--
RyGuy

--

Dave Peterson

Count all "Z" in a Range
 

Just be aware that SUBSTITUTE is case sensitive.

A1 = zero
A2 = Zebra

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"Z","")))

=1

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),"Z","")))

=2

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),"z","")))

=0

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
Very elegant!!

Regards,
Ryan---


--
RyGuy


"JP" wrote:

From: http://support.microsoft.com/kb/213889

Formula to Count the Number of Occurrences of a Single Character in a
Range

=SUM(LEN(range)-LEN(SUBSTITUTE(range,"a","")))
where range is the cell range in question, and "a" is replaced by the
character you want to count.

Note The above formula must be entered as an array formula. To enter a
formula as an array formula in Excel, press CTRL+SHIFT+ENTER.

(note this will only find "Z", not "z")


HTH,
JP

On Jan 8, 2:58 pm, ryguy7272
wrote:
I know this function will work on a specific cell:
=LEN(A1)-LEN(SUBSTITUTE(A1,"Z",""))

What about counting all letters Z in range A1:A10?
Seems simple. I tried a few things; nothing has worked yet...

Thanks,
Ryan--

--
RyGuy