Page 313 - Excel 2007 Bible

19_044039 ch14.qxp 11/21/06 11:06 AM Page 270
Part II
Working with Formulas and Functions
Let a Wizard Create Your Formula
xcel ships with an add-in called Conditional Sum Wizard. After you install this add-in, you can invoke the
Ewizard by choosing Formulas ➪ Solutions ➪ Conditional Sum.
You can specify various conditions for your summing, and the add-in creates the formula for you (always an
array formula). The Conditional Sum Wizard add-in, although a handy tool, is not all that versatile. For exam-
ple, you can combine multiple criteria by using an And condition but not an Or condition.
To install the Conditional Sum Wizard add-in:
1. Choose Office ➪ Excel Options to display the Excel Options dialog box.
2. Click the Add-ins tab on the left.
3. Select Excel Add-Ins from the drop-down list labeled Manage.
4. Click Go to display the Add-Ins dialog box.
5. Place a check mark next to Conditional Sum Wizard.
6. Click OK.

Summing values based on a date comparison

The following formula returns the total invoice amounts that have a due date after May 1, 2007:
=SUMIF(DateDue,”>=”&DATE(2007,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)

270