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)
Search JabSto ::

Custom Search