XL: Using a Logical AND or OR in a SUM+IF Statement in ExcelID: Q77676
|
In Microsoft Excel, attempting to use the logical functions AND and/or
OR inside a SUM+IF statement to test a range for more than one
condition does not work properly. A nested IF statement provides this
functionality. However, this article discusses a second, easier method
that uses the following formula:
=SUM(IF(Test1*Test2*...*Testn))
Use a SUM+IF statement to count the number of cells in a range
that pass a given test OR to sum those values in a range for which
corresponding values in another (or the same) range meet the specified
criteria. This acts similarly to the DSUM function in Microsoft Excel.
=SUM(IF(A1:A10>=1,IF(A1:A10<=10,1,0)))
=SUM(IF((A1:A10>=1)*(A1:A10<=10),1,0))
=SUM(IF((A1:A10>=DATEVALUE("1/10/99"))*(A1:A10<=DATEVALUE("2/10/99")),1,0))
TRUE*TRUE=1
TRUE*FALSE=0
FALSE*TRUE=0
FALSE*FALSE=0
=SUM(IF((A1:A10>=1)*(A1:A10<=10),B1:B10,0))
=SUM(IF((Test1)+(Test2)+...+(Testn),1,0))
"User's Guide," version 4.0, pages 153-157
"User's Guide," version 3.0, pages 119-120
Additional query words: 7.00 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 3.00 4.0 4.00 4.0a 4.00a 5.0 5.00 97 boolean conditional
Keywords : kbualink97
Version : MACINTOSH:2.x, 3.x, 4.x, 5.0,7.0; WINDOWS:2.x, 3.x, 4.x, 5.0, 5.0c,7.0
Platform : MACINTOSH WINDOWS winnt
Issue type : kbhowto
Last Reviewed: March 23, 1999