Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting cells that are 0 in a range of non-contiguous cells
I want to count how many of a group of specific cells contain a number 0
where the range of cells is non-contiguous or non-bordering. ie. not (A1:A10) Here is an example of what I am tyring, but it is not working correctly: =COUNTA("R46,R73,R99,R125,R151,R177,R203","0") The cell references in quotes are the 'range' and 0 is the value. All of the cells in the range have formulas, but only 2 of them currently result in a value 0, so the total should be 2. As the other formulas create values, that number would go up. I can't find any solid explanation of how to count to a cell range of specific cells vs. a row, cloumn or table of cells. Any suggestions? thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting cells that are 0 in a range of non-contiguous cells
One way:
As long as the cells will *ONLY* contain numbers (no formula blanks either): =(R460)+(R730)+(R990)+(R1250)+(R1510)+(R1770 )+(R2030) Biff "Mark" wrote in message ... I want to count how many of a group of specific cells contain a number 0 where the range of cells is non-contiguous or non-bordering. ie. not (A1:A10) Here is an example of what I am tyring, but it is not working correctly: =COUNTA("R46,R73,R99,R125,R151,R177,R203","0") The cell references in quotes are the 'range' and 0 is the value. All of the cells in the range have formulas, but only 2 of them currently result in a value 0, so the total should be 2. As the other formulas create values, that number would go up. I can't find any solid explanation of how to count to a cell range of specific cells vs. a row, cloumn or table of cells. Any suggestions? thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting cells that are 0 in a range of non-contiguous cells
Select the non-contiguous cells while
holding down CTRL. Enter a name in the Name Box, say ListA. =COUNTIF(INDEX(ListA,,,1):INDEX(ListA,,,COUNTA(Lis tA)),"0") |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting cells that are 0 in a range of non-contiguous cells
"Herbert Seidenberg" wrote...
Select the non-contiguous cells while holding down CTRL. Enter a name in the Name Box, say ListA. =COUNTIF(INDEX(ListA,,,1):INDEX(ListA,,,COUNTA(Li stA)),"0") Put the following values in A1:A10. 9 0 2 11 3 2 8 6 0 0 Select A1, A3, A5, A7, A9 and name it ListA. Enter your formula in C1. It returns 7, even though ListA has only 5 cells. Can you figure out why? Conditional counting over multiple cell ranges is easiest with FREQUENCY, e.g., to count the values 0 in ListA as above, =INDEX(FREQUENCY(ListA,{0}),2) and to count the values = 9, =INDEX(FREQUENCY(ListA,{8.99999999999999;9}),2) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting cells that are 0 in a range of non-contiguous cells
Harlan:
Thanks for pointing out my error. Your solution works great. Do you have a workaround for the 1000 character limit in the name ListA? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting cells that are 0 in a range of non-contiguous cells
Select the non contiguous cells while holding down CTRL, right click to copy,
select a empty cell and Paste Special, select Values, click OK Now you have a range of contiguous cells =COUNTIF(range of contiguous cells,"0") "Mark" wrote: I want to count how many of a group of specific cells contain a number 0 where the range of cells is non-contiguous or non-bordering. ie. not (A1:A10) Here is an example of what I am tyring, but it is not working correctly: =COUNTA("R46,R73,R99,R125,R151,R177,R203","0") The cell references in quotes are the 'range' and 0 is the value. All of the cells in the range have formulas, but only 2 of them currently result in a value 0, so the total should be 2. As the other formulas create values, that number would go up. I can't find any solid explanation of how to count to a cell range of specific cells vs. a row, cloumn or table of cells. Any suggestions? thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting cells that are 0 in a range of non-contiguous cells
Do you have a workaround for the 1000 character
limit in the name ListA? I can never get more than 255 chars. Use multiple area references: =INDEX(FREQUENCY((rng1,rng2),{0}),2) Biff "Herbert Seidenberg" wrote in message ups.com... Harlan: Thanks for pointing out my error. Your solution works great. Do you have a workaround for the 1000 character limit in the name ListA? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting cells that are 0 in a range of non-contiguous cells
Try a checkerboard.
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting cells that are 0 in a range of non-contiguous cells
It returns 7, even though ListA has only 5 cells. Can you figure out
why? I don't know if that's a rhetorical question but I'll answer it for the sake of others that may be interested. =COUNTIF(INDEX(ListA,,,1):INDEX(ListA,,,COUNTA(Lis tA)),"0") INDEX(ListA,,,1) = INDEX(A1,A3,A5,A7,A9,,,1) = A1 INDEX(ListA,,,COUNTA(ListA)) = INDEX(A1,A3,A5,A7,A9,,,COUNTA(A1,A3,A5,A7,A9)) = INDEX(A1,A3,A5,A7,A9,,,5) = A9 =COUNTIF(A1:A9,"0") = 7 It also would have crashed if there were any empty cells in the named range. Biff "Harlan Grove" wrote in message ups.com... "Herbert Seidenberg" wrote... Select the non-contiguous cells while holding down CTRL. Enter a name in the Name Box, say ListA. =COUNTIF(INDEX(ListA,,,1):INDEX(ListA,,,COUNTA(L istA)),"0") Put the following values in A1:A10. 9 0 2 11 3 2 8 6 0 0 Select A1, A3, A5, A7, A9 and name it ListA. Enter your formula in C1. It returns 7, even though ListA has only 5 cells. Can you figure out why? Conditional counting over multiple cell ranges is easiest with FREQUENCY, e.g., to count the values 0 in ListA as above, =INDEX(FREQUENCY(ListA,{0}),2) and to count the values = 9, =INDEX(FREQUENCY(ListA,{8.99999999999999;9}),2) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting cells that are 0 in a range of non-contiguous cells
Guys, thanks this one with frequency does exactly what I needed.
"Harlan Grove" wrote: "Herbert Seidenberg" wrote... Select the non-contiguous cells while holding down CTRL. Enter a name in the Name Box, say ListA. =COUNTIF(INDEX(ListA,,,1):INDEX(ListA,,,COUNTA(Li stA)),"0") Put the following values in A1:A10. 9 0 2 11 3 2 8 6 0 0 Select A1, A3, A5, A7, A9 and name it ListA. Enter your formula in C1. It returns 7, even though ListA has only 5 cells. Can you figure out why? Conditional counting over multiple cell ranges is easiest with FREQUENCY, e.g., to count the values 0 in ListA as above, =INDEX(FREQUENCY(ListA,{0}),2) and to count the values = 9, =INDEX(FREQUENCY(ListA,{8.99999999999999;9}),2) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pasting non-contiguous range of cells to new row, same cell locati | New Users to Excel | |||
Counting cells base on a predetermined range | Excel Worksheet Functions | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Counting empty cells within a range of cells | New Users to Excel | |||
counting cells in a range | Excel Discussion (Misc queries) |