Microsoft Office Tutorials and References
In Depth Information
Conditional Sums Using Multiple Criteria
In SUMIFS, the first argument is the range to be summed. The remaining arguments define the
criteria and come in pairs. Each pair consists of the criteria range followed by the criteria.
For use with earlier versions of Excel, the following array formula also does the job:
This formula creates two new arrays (in memory):
h A Boolean array that consists of TRUE if the corresponding Difference value is less than
zero; FALSE otherwise
h A Boolean array that consists of TRUE if the corresponding Office value equals Oregon;
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 arrays 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:
Using Or criteria
Suppose you want to get a sum of past-due invoice amounts, or ones associated with the Oregon
office. In other words, the value in the Amount range will be summed if either of the following
criteria is met:
h The corresponding value in the Difference range is negative.
h The corresponding text in the Office range is Oregon.