Page 312 - Excel 2007 Bible

19_044039 ch14.qxp 11/21/06 11:06 AM Page 269
Creating Formulas That Count and Sum
FIGURE 14.13
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 14
total number of past-due days for all invoices. For this worksheet, the formula returns –63.
=SUMIF(Difference,”<0”)
Because you omit the third argument, the second argument (“<0”) applies to the values in the Difference
range.
You don’t need to hard-code the arguments for the SUMIF function into your formula. For example, you
can create a formula, such as the following, which gets the criteria argument from the contents of cell G2:
=SUMIF(Difference,G2)
This formula returns a new result if you change the criteria in cell G2.

Summing values based on a different range
The following formula returns the sum of the past-due invoice amounts (in column C):
=SUMIF(Difference,”<0”,Amount)
This formula uses the values in the Difference range to determine if the corresponding values in the Amount
range contribute to the sum.

Summing values based on a text comparison
The following formula returns the total invoice amounts for the Oregon office:

=SUMIF(Office,”=Oregon”,Amount)
Using the equal sign is optional. The following formula has the same result:
=SUMIF(Office,”Oregon”,Amount)
To sum the invoice amounts for all offices except Oregon, use this formula:
=SUMIF(Office,”<>Oregon”,Amount)

269