Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA help Range Count | Excel Discussion (Misc queries) | |||
Using a count in a range name | Excel Discussion (Misc queries) | |||
Sum or Count using set range | Excel Worksheet Functions | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions |