Microsoft Office Tutorials and References

In Depth Information

**Conditional Sums Using Multiple Criteria**

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)

Text comparisons are not case-sensitive.

Summing values based on a date comparison

The following formula returns the total invoice amounts that have a due date after May 1, 2010:

=SUMIF(DateDue,”>=”&DATE(2010,5,1),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 formula that follows returns the total invoice amounts that have a future due date (including

today):

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

Conditional Sums Using Multiple Criteria

The examples in the preceding section all use a single comparison criterion. The examples in this

section involve summing cells based on multiple criteria.

Figure 7-16 shows the sample worksheet again, for your reference. The worksheet also shows the

result of several formulas that demonstrate summing by using multiple criteria.