XL: The Conditional Sum Wizard for Microsoft Excel

ID: Q147275

The information in this article applies to:

Microsoft Excel for Windows, version 5.x
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel 98 Macintosh Edition

SUMMARY

The Conditional Sum Wizard is part of a series of add-on wizards that Microsoft is making available to enhance your use of Microsoft Excel. The Conditional Sum Wizard helps you to write formulas that calculate the sum of values that meet specified conditions.

Note: The Conditional Sum Wizard is included with Excel 97 for Windows and Excel 98 Macintosh Edition.

The add-in in this article is provided "as is" and Microsoft does not guarantee that it can be used in all situations. Although Microsoft Technical Support professionals can help with the installation and existing functionality of this add-in, they will not modify the add-in to provide new functionality.

MORE INFORMATION

The Conditional Sum Wizard aids in writing formulas that will sum a set of values that meet specified conditions. This wizard provides a user interface to create SUM-IF formulas. Below is a detailed description of each step in the Wizard, followed by an example.

Step 1 of the Wizard

In this step, you need to specify the range of cells containing the data that will be used for the formula. This range must include column headings and the cells containing the condition parameters. For the most part, this means selecting the entire list on the worksheet.

Like most of the Microsoft Excel add-ins, the dialog box opens with the Range Edit box filled in. Microsoft Excel (versions 5.0 and later) is designed to determine where your list is. If the range is not filled in correctly, you must change it by selecting the correct range (with the mouse) or by typing it in manually.

NOTE: It is important to have column headings in the data range specified in this step. The column headings are used in the following steps of the wizard.

Step 2 of the Wizard

First, you need to determine what values to sum if all the specified conditions are met. There is a drop-down list that contains all the column headings in the data range that you specified in Step 1. From this list, choose the column that will be used for the sum.

Second, you need to specify the conditions for the sum. Each condition will have a condition parameter (column heading), an operator, and a value. For each condition, select a condition parameter and an operator from the lists that are provided. You can either select the value for the condition from the provided list or you can type in the value. After you set up each condition, click the Add Condition button to add it to the list of conditions. If you make a mistake or if you need to change a condition, use the Delete Condition button.

Step 3 of the Wizard

In step 3, the wizard is ready to copy the formula to the worksheet. The wizard provides options for how it copies the formula to the worksheet. You can choose either of the following options:

Copy only the sum that results from the current parameters.

This copies the formula to the worksheet. This is selected by default.

Include current parameters in the worksheet, so that they can easily be changed.

This copies the formula as well as the values of the condition parameters to the worksheet. If you choose this option, you can change the values of the condition parameters without having to modify the formula or go through the Wizard again.

Step 4 of the Wizard

The information needed in this step varies depending on which option you choose in step 3 of the wizard. Do either of the following steps:

If you chose the first option (that is, copy only the sum that results from the current parameters), in this step you need to provide the cell reference of where the formula is to be placed on the worksheet. You can select the cell with the mouse, or you can type in the cell reference.

If you chose the second option in step 3 of the wizard (that is, include current parameters in the worksheet), you need to provide the cell reference where the first condition parameter value will be placed. You can select the cell with the mouse or you can type in the cell reference. The wizard contains an additional step for each condition that you specified in Step 2 of the wizard. Finally, you are asked for a cell reference for where to place the formula.

NOTE: Using the Conditional Sum Wizard does not increase the limit of seven nested IF functions in Microsoft Excel. The Wizard disables the Add Condition button when you reach the limit of seven conditions.

How to obtain the Conditional Sum Wizard

NOTE: Before you can use this example, you must install the add-in. Please download the file and see the Readme.txt file for the installation instructions.

The add-in discussed in this article can be obtained from the Microsoft TechNet compact disc and from Online Services.

For additional information on downloading a file from Online Services, please see the following article in the Microsoft Knowledge Base:

Q119591 How to Obtain Microsoft Support Files From Online Services

Example of the Conditional Sum Wizard

In a new Microsoft Excel worksheet, type the following: A1: Region B1: Date C1: Total Sales D1: Sales Representative A2: South B2: 1/1/96 C2: $103,476.98 D2: John Smith A3: East B3: 1/1/96 C3: $57,400.35 D3: John Smith A4: North B4: 1/5/96 C4: $95,375.34 D4: Mary Jones A5: East B5: 1/9/96 C5: $143,564.67 D5: John Smith A6: West B6: 1/9/96 C6: $200,359.85 D6: Mary Jones A7: North B7: 1/9/96 C7: $204,014.46 D7: Mary Jones A8: South B8: 1/9/96 C8: $98,435.12 D8: Mary Jones A9: West B9: 1/13/96 C9: $65,678.41 D9: John Smith A10: West B10: 1/14/96 C10: $138,357.60 D10: John Smith A11: East B11: 1/23/96 C11: $69,189.58 D11: John Smith A12: North B12: 1/23/96 C12: $40,781.30 D12: Mary Jones A13: East B13: 1/25/96 C13: $89,053.68 D13: Mary Jones A14: North B14: 1/31/96 C14: $85,462.12 D14: John Smith A15: South B15: 1/31/96 C15: $199,980.15 D15: Mary Jones

On the Tools menu, click Conditional Sum Wizard.

In Step 1 of the wizard, the range $A$1:$D$15 should be filled in. If it is not, please type in the correct range. Click Next.

In Step 2 of the wizard, select Total Sales as the column to sum and add the following conditions:
Sales Representative = Mary Jones
Date > 1/10/96

Click the Next button.

In Step 3 of the wizard, select "No. Copy only the sum that results from the current parameters." Click the Next button.

In Step 4, the cell reference on the worksheet to copy the formula to is $E$1. Click the Finish button. The answer in E1 will be $329,815.13, and the formula will be:
```
      {=SUM(IF($B$2:$B$15>DATEVALUE("1/10/96"),IF($D$2:$D$15="Mary
      Jones",$C$2:$C$15,0),0))} 
```

Additional query words:

Keywords : kbtool xlformula
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.x,7.0; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type : kbinfo