Using SUM(IF()) as an array function instead of COUNTIF() with AND or OR criteria in Excel

Article translations Article translations
Article ID: 275166 - View products that this article applies to.
This article was previously published under Q275166
Expand all | Collapse all

SUMMARY

In Microsoft Excel, the COUNTIF() function allows you to determine the number of cells in a range whose values match a certain criterion, but it does not allow you to use criteria involving the logical operators OR or AND. This article shows you how to use SUM(IF()) as an array function to accomplish that task.

MORE INFORMATION

The COUNTIF() function, introduced in Excel version 5.0, provides a convenient way to count, within a range, the cells whose values match a certain criterion. For example, if a worksheet has the following values in cells A1:J1
    1    6    5    1    5    2    1    8    4    7
				
you can type the function =COUNTIF(A1:J1, 5) in cell A3, where it produces the result 2, that being the number of cells in the range A1:J1 with the value 5.

However, if you want to count the number of cells whose values are either 5 or 1, the COUNTIF() function is not appropriate because it does not recognize compound criteria such as "5 OR 1." For that purpose, you need to use SUM(IF()) as an array function.

To obtain this result for the preceding data, follow these steps:
  1. Start Microsoft Excel with a new worksheet.
  2. Type the values listed earlier in this section in cells A1:J1 of the worksheet.
  3. In cell A3, type the following function
    =SUM(IF((A1:J1=5)+(A1:J1=1), 1, 0))
    and then press CTRL+SHIFT+ENTER simultaneously to enter the array formula.
You receive the result 5, which is correct because there are two 5's and three 1's in the data range.

REFERENCES

For more information about using SUM(IF()) as an array function, click the following article number to view the article in the Microsoft Knowledge Base:
267982 How to use a logical AND or OR in a SUM+IF statement in Excel

Properties

Article ID: 275166 - Last Review: September 18, 2011 - Revision: 7.0
APPLIES TO
  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Office Excel 2003
  • Microsoft Excel 2010
Keywords: 
kbhowto kbinfo KB275166

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com