Microsoft Office Tutorials and References
In Depth Information
Calculating Single-Criterion Conditional Sums
Calculating Single-Criterion Conditional Sums
The SUM function is easily the most commonly used function. But sometimes you need more
flexibility than the SUM function provides. The SUMIF function is useful when you need to
compute conditional sums. For example, you might need to calculate the sum of just the negative
values in a range of cells.
The examples in this tip demonstrate how to use the SUMIF function for conditional sums by
using a single criterion.
These formulas are based on the worksheet shown in Figure 118-1, which is set up to track
invoices. Column F contains a formula that subtracts the date in column E from the date in
column D. A negative number in column F indicates that the payment is past due. The worksheet
uses named ranges that correspond to the labels in row 1.
Figure 118-1: A negative value in column F indicates a past-due payment.
Summing only negative values
The following formula returns the sum of the negative values in column F. In other words, it
returns the total number of past-due days for all invoices (for this worksheet, the formula
returns –63):
=SUMIF(Difference,”<0”)
The SUMIF function can use three arguments. Because you omit the third argument, the second
argument ( “<0” ) applies to the values in the Difference range.
 
Search JabSto ::




Custom Search