ID: Q147275
The information in this article applies to:
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.
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.
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.
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.
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:
This copies the formula to the worksheet. This is selected by default.
-or-
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.
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:
-or-
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
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
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.
the following conditions:
Sales Representative = Mary Jones
Date > 1/10/96
Click the Next button.
the current parameters." Click the Next button.
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))}
Keywords : kbtool xlformula
Version : WINDOWS:5.0,7.0,97; MACINTOSH:5.0,5.0a,98
Platform : MACINTOSH WINDOWS
Issue type : kbinfoLast Reviewed: May 17, 1999