XL: Using a Logical AND or OR in a SUM+IF Statement in Excel

 Last reviewed: September 11, 1997
Article ID: Q77676
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for the Macintosh, versions 2.x, 3.x, 4.x, 5.0

SUMMARY

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 gives this functionality. However, this article discusses a second easier method that uses the following formula: 

   {=SUM(IF(Test1*Test2*...*Testn))}

MORE INFORMATION

A SUM+IF statement is used 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 Microsoft Excel DSUM function.

Example

This example counts the number of values in the range A1:A10 that fall between 1 and 10, inclusively.

This can be accomplished by using the following nested IF statement: 

   {=SUM(IF(A1:A10>=1,IF(A1:A10<=10,1,0)))}
The following method also works and is much easier to read if multiple tests are to be conducted: 

   {=SUM(IF((A1:A10>=1)*(A1:A10<=10),1,0))}
NOTE: Both formulas should be entered as array formulas by pressing CTRL+SHIFT+ENTER simultaneously.

With this method, you are multiplying the results of one logical test by another logical test to return TRUEs and FALSEs to our SUM function. These can be equated to: 

   TRUE*TRUE=1
   TRUE*FALSE=0
   FALSE*TRUE=0
   FALSE*FALSE=0
The method shown above counts the number of cells in the range A1:A10 for which both tests evaluate to TRUE. To sum values in corresponding cells (for example, B1:B10), modify the formula as shown below: 

   {=SUM(IF((A1:A10>=1)*(A1:A10<=10),B1:B10,0))}
You can implement an OR in a SUM+IF statement similarly. To do this, modify the formula shown above by replacing the multiplication sign (*) with a plus sign (+). This gives the following generic formula: 

   {=SUM(IF((Test1)+(Test2)+...+(Testn),1,0))}

REFERENCES

"User's Guide," version 4.0, pages 153-157

"User's Guide," version 3.0, pages 119-120

Additional reference 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
Platform : WINDOWS
Issue type : kbhowto

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 11, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.