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.

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.

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")

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)

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?

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.

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?

counting cells that are 0 in a range of non-contiguous cells
 

Try a checkerboard.

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)

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)