Count all “Z†in a Range

R

ryguy7272

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--
 
J

JP

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
 
R

ryguy7272

Very elegant!!

Regards,
Ryan---


--
RyGuy


JP said:
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
Click to expand...
 
D

Dave Peterson

You could use:

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

and not have to array enter the formula.
 
T

T. Valko

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
 
Top