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

Search JabSto ::

Custom Search