Hello again.

I have a spreadsheet with 46 columns (will be 52) and (so far) 1600 rows.
The cells in each column and row contains a number equal to or greater than
zero. What I would like to do is;
1. count the number of cells in column 2 with a value 0, if the value in
column 1 in the same row is = 0.
2. for each subsequent column (i.e. column 3) count the number of cells with
a value 0, if the value in the previous columns (1 & 2) & corresponding rows
= 0.

Logic tells me it has got to be a combination of IF, AND & COUNTIF OR
SUMPRODUCT but I am not able to come up with the right solution.

=SUMPRODUCT(--(A2:A2000=0),--(B2:B20000)

=SUMPRODUCT(--(A2:A2000=0),--(B2:B2000=0),--(C2:C200000)


"ronnomad" wrote:

Hello again.

I have a spreadsheet with 46 columns (will be 52) and (so far) 1600 rows.
The cells in each column and row contains a number equal to or greater than
zero. What I would like to do is;
1. count the number of cells in column 2 with a value 0, if the value in
column 1 in the same row is = 0.
2. for each subsequent column (i.e. column 3) count the number of cells with
a value 0, if the value in the previous columns (1 & 2) & corresponding rows
= 0.

Logic tells me it has got to be a combination of IF, AND & COUNTIF OR
SUMPRODUCT but I am not able to come up with the right solution.

Teethless Mama,

Sorry, did not see your response until today. Your suggestion works but, by
the time I get to the 10th or 20th column it's going to get a bit cumbersome.
Is there no way to throw a range into Sumproduct (i.e.
--(a2:b2000=0),--(c2:c20000)? - I know this does not work but I am seeking a
shortcut (although using your formula only requires that I add one variable
as I copy to the next column).

Thanks,

Ron

"Teethless mama" wrote:


=SUMPRODUCT(--(A2:A2000=0),--(B2:B20000)

=SUMPRODUCT(--(A2:A2000=0),--(B2:B2000=0),--(C2:C200000)


"ronnomad" wrote:

Hello again.

I have a spreadsheet with 46 columns (will be 52) and (so far) 1600 rows.
The cells in each column and row contains a number equal to or greater than
zero. What I would like to do is;
1. count the number of cells in column 2 with a value 0, if the value in
column 1 in the same row is = 0.
2. for each subsequent column (i.e. column 3) count the number of cells with
a value 0, if the value in the previous columns (1 & 2) & corresponding rows
= 0.

Logic tells me it has got to be a combination of IF, AND & COUNTIF OR
SUMPRODUCT but I am not able to come up with the right solution.

Teethless mama,

I went ahead an started to enter your suggestion but, I have run into the 30
array limit for Sumproduct.

Ronnomad

"Teethless mama" wrote:


=SUMPRODUCT(--(A2:A2000=0),--(B2:B20000)

=SUMPRODUCT(--(A2:A2000=0),--(B2:B2000=0),--(C2:C200000)


"ronnomad" wrote:

Hello again.

I have a spreadsheet with 46 columns (will be 52) and (so far) 1600 rows.
The cells in each column and row contains a number equal to or greater than
zero. What I would like to do is;
1. count the number of cells in column 2 with a value 0, if the value in
column 1 in the same row is = 0.
2. for each subsequent column (i.e. column 3) count the number of cells with
a value 0, if the value in the previous columns (1 & 2) & corresponding rows
= 0.

Logic tells me it has got to be a combination of IF, AND & COUNTIF OR
SUMPRODUCT but I am not able to come up with the right solution.