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:

=SUMIFS(Amount,Difference,”<0”,Office,”Oregon”)