Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting problem | Excel Worksheet Functions | |||
Problem with counting characters in a cell | Excel Worksheet Functions | |||
Excel Display Problem | Excel Discussion (Misc queries) | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) | |||
Counting problem again! | Excel Worksheet Functions |