Microsoft Office Tutorials and References

In Depth Information

**Conditional Sums Using a Single Criterion**

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 can also use the following array formula to sum the negative values in the

Difference
range:

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

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.

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-15):

=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))}