Microsoft Office Tutorials and References

In Depth Information

**Summing values based on a different range**

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

Summing values based on a date comparison

The following formula returns the total invoice amounts that have a due date of May 10, 2010,

or later:

=SUMIF(DateDue,”>=”&DATE(2010,5,10),Amount)

Notice that the second argument for the SUMIF function is an expression. The expression uses

the DATE function, which returns a date. Also, the comparison operator, enclosed in quotation

marks, is concatenated (using the & operator) with the result of the DATE function.

The following formula returns the total invoice amounts that have a future due date (including

today):

=SUMIF(DateDue,”>=”&TODAY(),Amount)