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)
 
Search JabSto ::




Custom Search