Microsoft Office Tutorials and References

In Depth Information

**Conditional Sums Using a Single Criterion**

Conditional Sums Using a Single Criterion

Often, you need to calculate a
conditional sum.
With a conditional sum, values in a range

that meet one or more conditions are included in the sum. This section presents examples

of conditional summing by using a single criterion.

The
SUMIF
function is very useful for single-criterion sum formulas. The
SUMIF
function

takes three arguments:

range
: The range containing the values that determine whether to include a

particular cell in the sum

■

criteria
: An expression that determines whether to include a particular cell in

the sum

■

17

sum_range
: Optional. The range that contains the cells you want to sum. If you

omit this argument, the function uses the range speciﬁ ed in the ﬁ rst argument.

■

The examples that follow demonstrate the use of the
SUMIF
function. These formulas are

based on the worksheet shown in Figure 17.14, 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 a past-due payment. The worksheet uses named ranges that

correspond to the labels in row 1.

FIGURE 17.14

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")