Microsoft Office Tutorials and References
In Depth Information
Conditional Sums Using Multiple Criteria
Figure 7-16: This worksheet demonstrates summing based on multiple criteria.
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/criterion 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 Oregon office. In other words, the value in the Amount range will be summed only
if both of the following criteria are met:
h The corresponding value in the Difference range is negative.
h The corresponding text in the Office range is Oregon.
The SUMIFS function was designed for just this task: