XL: The Conditional Sum Wizard for Microsoft Excel

• 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, version 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 Engineers 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.

      -or-
  

• 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.

      -or-
  

• 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.

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:

   ARTICLE-ID: Q119591
   TITLE:      How to Obtain Microsoft Support Files From Online Services

Example of the Conditional Sum Wizard

1. 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
   
   

2. On the Tools menu, click Conditional Sum Wizard.

3. 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.

4. 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.

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

6. 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))}