counting problem

L

LucasBuck

How could I make a function that would look at two columns of data, and
count how many have a value of zero in both cells of a row? ie
1 2
3 5
0 0
2 2
And I would get a count of 1 to display in a cell reporting how many
sets of 0 there are
 
S

swatsp0p

Use the SUMPRODUCT function as such:

=SUMPRODUCT(--(A1:A100=0),--(B1:B100=0))

of course, adjust the ranges to match your data.

HTH

Bruce
 
P

Paul Sheppard

LucasBuck said:
How could I make a function that would look at two columns of data, and
count how many have a value of zero in both cells of a row? ie
1 2
3 5
0 0
2 2
And I would get a count of 1 to display in a cell reporting how many
sets of 0 there are
Click to expand...

Hi LucasBuck

Assuming your example data above to be in cells A1 - B4, in c1 enter
this formula > =IF(AND(A1=0,B1=0),1) and copy down to C4

When there are 2 zeros it will return 1, any other combination will
return FALSE

In C5 enter this formula =SUM(C1:C4)

If you don't want to show false change the formula to read >
=IF(AND(A1=0,B1=0),1,0) in which case it will return 0 instead of false
 
R

Roger Govier

Hi Lucas

One way
=SUMPRODUCT(--(G1:G5=0),--(H1:H5=0))
Change ranges to suit

Regards

Roger Govier
 
Top