counting problem
 

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


--
LucasBuck
------------------------------------------------------------------------
LucasBuck's Profile: http://www.excelforum.com/member.php...o&userid=19710
View this thread: http://www.excelforum.com/showthread...hreadid=467158

Use the SUMPRODUCT function as such:

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

of course, adjust the ranges to match your data.

HTH

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=467158

LucasBuck Wrote:
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

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


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=467158

Hi Lucas

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

Regards

Roger Govier


LucasBuck wrote:
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

=SUMPRODUCT(--(A1:A10=0),--(B1:B10=0),--(A1:A10<""))

--
HTH

Bob Phillips

"LucasBuck" wrote
in message ...

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


--
LucasBuck
------------------------------------------------------------------------
LucasBuck's Profile:
http://www.excelforum.com/member.php...o&userid=19710
View this thread: http://www.excelforum.com/showthread...hreadid=467158