Count all “Z†in a Range

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

 

[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

 

[ryguy7272]

Very elegant!!

Regards,
Ryan---


--
RyGuy

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

 

[Dave Peterson]

You could use:

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

and not have to array enter the formula.

 

[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