Microsoft Office Tutorials and References
In Depth Information
The SUMIFS function (introduced in Excel 2007) can be used to sum a range when multiple conditions are met.
The first argument of SUMIFS is the range to be summed. The remaining arguments are 1 to 127 range/cri-
terion pairs that determine which values in the sum range are included. In the following examples, alternatives
to SUMIFS are presented for those workbooks that are required to work in versions prior to 2007.
Using And criteria
Suppose you want to get a sum of both the invoice amounts that are past due as well as associated with the Ore-
gon office. In other words, the value in the Amount range will be summed only if both of the following criteria
• The corresponding value in the Difference range is negative.
• The corresponding text in the Office range is Oregon.
The SUMIFS function was designed for just this task:
For use with versions prior to Excel 2007, the following array formula also does the job:
This formula creates two new arrays (in memory):
• A Boolean array that consists of TRUE if the corresponding Difference value is less than zero; FALSE oth-
• A Boolean array that consists of TRUE if the corresponding Office value equals Oregon; FALSE otherwise
Multiplying Boolean values result in the following:
• TRUE * TRUE = 1
• TRUE * FALSE = 0
• FALSE * FALSE = 0
Therefore, the corresponding Amount value returns nonzero only if the corresponding values in the memory ar-
rays are both TRUE. The result produces a sum of the Amount values that meet the specified criteria.
You may think that you can rewrite the previous array function as follows, using the
SUMPRODUCT function to perform the multiplication and addition:
For some reason, the SUMPRODUCT function does not handle Boolean values properly, so the formula
does not work. The following formula, which multiplies the Boolean values by 1, does work: