Select the product you need help with
Using SUM(IF()) as an array function instead of COUNTIF() with AND or OR criteria in ExcelArticle ID: 275166 - View products that this article applies to. This article was previously published under Q275166 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
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:
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
(http://support.microsoft.com/kb/267982/
)
How to use a logical AND or OR in a SUM+IF statement in Excel
Properties | Article Translations
 |
Back to the top
