Part II: Working with Formulas and Functions
304
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”)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 exam-
ple, you can create a formula, such as the following, which gets the criteria argument from the con-
tents 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 whether 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 in the argument 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)