Microsoft Office Tutorials and References

In Depth Information

**Calculating Multiple-Criterion Conditional Sums**

Calculating Multiple-Criterion Conditional Sums

Tip 118 contains summing examples that use a single comparison criterion. The examples in this

tip involve summing cells based on multiple criteria. Because the SUMIF function doesn’t work

with multiple criteria, you need to use an array formula.

Figure 119-1 shows the sample worksheet again, for your reference. The formulas in this tip, of

course, can be adapted to your own worksheets.

Figure 119-1:
This worksheet demonstrates summing based on multiple criteria.

Using “And” criteria

Suppose that you want to get a sum of the invoice amounts that are past due
and
associated

with the Oregon office. In other words, the value in the Amount range is summed only if both of

these criteria are met:

h
The corresponding value in the Difference range is negative.

h
The corresponding text in the Office range is
“Oregon”
.

The following formula, which uses the new SUMIFS function, does the job:

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

The first argument of the SUMIFS function is the range to be summed. Subsequent arguments

come in pairs: the range on which the criterion is based, followed by the actual criterion.

If you plan to share your work with someone who uses an earlier version of Excel, you can’t use

the SUMIFS function. The following formula returns the same result:

=SUMPRODUCT(1*(Difference<0),1*(Office=”Oregon”),Amount)