Excel 2010 Bible

Chapter 13: Creating Formulas That Count and Sum

305

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 quotes, is concat-
enated (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 used a single comparison criterion. The examples in this

section involve summing cells based on multiple criteria.

Figure 13.15 shows the sample worksheet again, for your reference. The worksheet also shows the

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

Using And criteria ...................................................................................................

Suppose that you want to get a sum of the invoice amounts that are past due and associated with

the Oregon office. In other words, the value in the Amount range will be summed only if both of

the following criteria are met:

l (^) The corresponding value in the Difference range is negative.
l The corresponding text in the Office range is Oregon.
If the worksheet won’t be used by anyone running a version prior to Excel 2007, the following for-
mula does the job:
=SUMIFS(Amount,Difference,”<0”,Office,”Oregon”)
The array formula that follows returns the same result and will work in all versions of Excel.
{=SUM((Difference<0)(Office=”Oregon”)Amount)}