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.