Microsoft Office Tutorials and References

In Depth Information

=SUMIF(Difference,”<0”)

Because you omit the third argument, the second argument ("<0") applies to the values in the
Difference
range.

You do not 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.

You can also use the following array formula to sum the negative values in the Differen-

ce range:

{=SUM(IF(Difference<0,Difference))}

Summing values based on a different range

The following formula returns the sum of the past-due invoice amounts (see column C in Figure 7-16):

=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.

You can also use the following array formula to return the sum of the values in the

Amount range, where the corresponding value in the Difference range is negative:

{=SUM(IF(Difference<0,Amount))}

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)