Count all “Z†in a Range

  • Thread starter Thread starter ryguy7272
  • Start date Start date
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--
 
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
 
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...
 
You could use:

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

and not have to array enter the formula.
 
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
 
Back
Top