Page 150 - Excel Workbook for Dummies

14_798452 ch09.qxp 3/13/06 7:52 PM Page 133
Chapter 9: Using Math Functions 133
The SUMIF function works great when you only have only one criterion that you want
to apply in doing the summing. It does not, however, work when you have multiple
criteria that you need to use in determining which numbers get added to the total and
which don’t. For those situations, you have to turn to the Conditional Sum Wizard, a
nifty little Excel add-in tool that walks you through the steps of building more com-
plex SUM formulas that utilize IF conditions that can include multiple criteria.

In the next exercise, you use the Conditional Sum Wizard to construct a formula using
the Chris’s Cookies monthly sales list in the Jan-06 Sales worksheet that sums the
daily sales made at the Anderson Road store location for all baked items except for
strawberry pie after the date of January 1, 2006.

Try It

Exercise 9-5: Using the Conditional Sum Wizard

Use the Solved9-4.xls workbook that you created in Exercise 9-4 to complete this exer-
cise using the Conditional Sum Wizard:

1. Select the Conditional Sum Wizard check box in the Add-Ins dialog box (Tools➪
Add-Ins) and then select Yes if prompted by an alert dialog box to install the
add-in.
2. Position the cell cursor in cell I10 to use the Conditional Sum Wizard to construct
the formula that sums all baked goods except for strawberry pie sold after
January 1 at the Anderson Road store location.
3. Open the Step 1 of 4 Conditional Sum Wizard dialog box by choosing Tools➪
Conditional Sum.
In this dialog box, you indicate the entire range of the data list, including its
column labels (field names). In this case, you need to select the cell range of the
entire data list A2:G62

4. Drag the cell cursor through the cell range A2:G62.
The text box in the Step 1 of 4 dialog box now contains the range ‘Jan-06
Sales’!$A$2:$G$62.
5. Select the Next button to proceed to the Step 2 of 4 Conditional Sum Wizard
dialog box.
In the Step 2 of 4 dialog box, you indicate the column to sum as well as the
column(s) to evaluate and the condition(s) to be used.
6. Leave Daily Sales in the Column to Sum text box, select Date in the Column drop-
down list box below, > (greater than) in the Is drop-down list box, and leave
1/1/2006 selected in This Value drop-down list box before you select the Add
Condition button.
7. Using these drop-down list boxes and the Add Condition button, add a second
condition where the Item is not equal to (<>) strawberry pie.

8. Add a third and final condition with these controls where the store is equal to (=)
Anderson Road.
9. Check your Step 2 of 4 Conditional Sum Wizard dialog box against the one shown
in Figure 9-4: If everything checks out, click the Next button.
10. In the Step 3 of 4 Conditional Sum Wizard dialog box, leave the Copy Just the
Formula to a Single Cell option button selected and then click the Next button.