Microsoft Office Tutorials and References

In Depth Information

**Summing values based on a different range**

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

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 (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 ofﬁ ce:

=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 ofﬁ ces
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 after May 1,

2013:

=SUMIF(DateDue,">="&DATE(2013,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

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