Article ID: 214249 - View products that this article applies to.
This article was previously published under Q214249
This article shows you how to use the Conditional Sum Wizard in Microsoft Excel.
The Conditional Sum Wizard helps you calculate the sums of values that meet specified conditions. It is included with Microsoft Excel versions 97 and later.
The add-in that is discussed in this article is provided as-is. Microsoft does not guarantee that it can be used in all situations. Although Microsoft 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. It is one of several add-ins that are included with Excel, but which are not loaded by default.
The following is a detailed description of each step in the wizard, followed by a description of how to install the wizard and an example of its use.
Step 1: Identifying the Data RangeIn this step, you specify the range of cells containing the data to be used for the conditional sum. This range must include column headings and the cells containing the data to be summed as well as data defining the conditions for inclusion in the sum. For the most part, this means selecting an entire list on the worksheet.
Like most of the Excel data tools, the wizard identifies a data list automatically if the selected cell is within the list when the wizard is started. If the selected cell is outside the list, you must identify it by selecting the correct range with the mouse or by typing the appropriate range description.
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: Identifying the Data to Be Summed and the Conditions for InclusionFirst, you need to select the column containing the values to sum if the specified conditions are met; use a selection list that contains the column headings in the data range that you specified in step 1.
Second, you need specify the conditions for the sum. Each condition has a condition parameter (a column heading), an operator, and a value. Selection lists are provided for the parameters and operators, and you can either select the value for the condition from the provided list or you can type a value that is not listed.
You can establish up to seven conditions. After you set up each condition, click Add Condition to add it to the list of conditions. If you make a mistake or if you need to change a condition, click Delete Condition to revise the list.
Step 3: Choosing the Type of OutputYou can choose one of two types of output from the Conditional Sum Wizard:
Step 4: Identifying the Output CellsThe information needed in this step depends on which option you choose in step 3. Perform one of the following steps:
How to Load the Conditional Sum WizardThe Conditional Sum Wizard is not loaded by default. To determine whether it has been loaded, on the Tools menu, look for the Wizard menu item. It it is not present, or if when you point to it, the item Conditional Sum is not present, you need to load the add-in.
To load the Conditional Sum Wizard, follow these steps:
(http://support.microsoft.com/kb/119591/EN-US/ )How to Obtain Microsoft Support Files From Online Services
Example using the Conditional Sum Wizard
Known issues that may occur when you use the Conditional Sum WizardIf the file name or the sheet name is too long, the Conditional Sum Wizard will fail with a "Runtime error 1004: FormulaArray method of Range class failed" error when the following conditions are true:
For more information about add-ins, click Microsoft Excel Help on the Help menu, type add-in programs for Microsoft Excel in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Article ID: 214249 - Last Review: January 24, 2007 - Revision: 2.4
Contact us for more help