Most tasks in Microsoft Excel spreadsheets can be handled with fairly straightforward formulas like =SUM() and =COUNT(). But sometimes you need to analyze the data in ways you didn't anticipate, and you need unusual measures. For example, suppose you have a large spreadsheet that contains a lot of data, and you discover you need some totals that can't be easily pulled from the sheet. Perhaps data is entered in a random fashion, but you frequently need totals based on a different arrangement of the data.
 |
| Figure 1. |
The spreadsheet in Figure 1 contains gasoline and mileage data. Let's assume it was organized as is, for whatever reason. At a later date, though, it becomes important to total gasoline costs based on dates. Sorting the spreadsheet would make it easy to resolve the problem and get the figures, but destroy the current sort of the data, which may be required for other purposes.
For a solution to the problem other than manually totaling the numbers you need, look to Excel's Conditional Sum Wizard. This is an add-in that comes with Excel beginning with Excel 97. This tool lets you total cells in one column (say Total) based on the values in another column (like Date). You could, for instance, get a total of costs for 1996 without having to rearrange your data or add the amounts manually, and the cell holding the total will continue to update automatically as data is added to your spreadsheet.
Step 1
 |
| Figure 2. |
 |
| Figure 3. |
The Conditional Sum Wizard is accessed by clicking Tools=>Wizard=>Conditional Sum... The wizard will pop up a series of dialog boxes to help you complete the formula. The first, seen in Fig. 2, is labeled Step 1 of 4 and asks you where it can find the data that will be totaled. Note that the wizard fills in the box with the dimensions of the spreadsheet, or section of spreadsheet, that you're currently using. Next to the data entry box is a small box with an underscore. Click on it and the dialog box will shrink out of the way (Fig. 3) so you can mark the area.
Once the dialog shrinks, the current section of your spreadsheet will be marked by the moving dashed line Excel uses. If you're working with only one sheet and one table, you might be able to just press [ENTER] and go on. Note that you can mark a section in another sheet in the same workbook, or even in another workbook.
This is the tricky part of using the Conditional Sum Wizard. The area you mark must include the two columns that contain 1) the data you're totaling, and 2) the column that determines which cells to sum. You must also mark the column headers-the text above the columns. You'll see why in a moment. Once you've marked the area you need, press [ENTER]. The dialog box will return to its original shape, with the area you've marked in the data entry box. Click on Next to continue.
Step 2
 |
| Figure 4. |
 |
| Figure 5. |
The resulting dialog box is labeled Step 2 of 4. The first entry, at the top of the box, lets you choose which column is to be added, selected by choosing the column header from a pop-up list. The default is the last column in the area you've selected, and in this case that's what we want to use: "Total."
Next is a section of three boxes that builds the comparison expression. We want to add all totals from 1996, so we'll specify two conditions. In the first box, click the down arrow (Fig. 4) to pop up a list of column headers. In this case, select "Date," the column we'll use to determine which rows to add.
After choosing "Date," click the down arrow in the second box to select an operator (Fig. 5). Here we're choosing ">=," which stands for "is more than or equals." The first condition will be that the date be more than or equal to January 1, 1996. In the third box, type in the date as "01/01/1996." You could also click on the down arrow for the third box and choose the contents of any cell in that column. This works well, for example, if you have a column listing different salespeople and want totals by salesperson. But be aware that every entry must be exactly the same in this column. If you have a space after the name one time, that row will not be included in your totals.
Once we have the correct specifications in the three boxes, click on Add Condition. This will put the condition we've just created into the display box. Then make a second expression, specifying "Date <= 12/31/1996" and click Add Condition again. When you have both conditions in the box, click Next.
Step 3
 |
| Figure 6. |
Step 3 of 4 (Fig. 6) gives you another choice. You can copy just the formula to another cell somewhere in your workbook, which will result in the desired total displaying there. Or you can copy the formula and the values it depends on (the dates) to a three-cell location. Make that choice and click Next again.
Step 4
This brings up a box that says, "Type or select a cell and then click Finish." You can just click a cell and click Finish, or you can click the down arrow in the selection box to shrink the box out of the way until you select a cell and press [ENTER]. When you click Finish, the total (or the total and conditional data) is copied to the cell you selected. Like any other formula, it will update the figure in the cell as conditions in the spreadsheet change.
Warning!
There is one very big caveat with the Conditional Sum Wizard. Normally in Excel, you can copy a formula to another cell and edit it to use different conditions. I expected to be able to copy the formula to another cell and edit the dates to show, say, 1997 instead of 1996. However, these formulas cannot be copied and edited. You have to run the wizard for every instance you want of conditional sums. This is true in both Excel 97 and Excel 2000.
The formulas created show locations with the absolute symbol ($), which means the number or column so marked will not change if you copy the formula to another cell. The formula will, adjust according to the normal rules if rows or columns are inserted in the spreadsheet.
