How to use the Conditional Sum Wizard in Microsoft Excel (214249)

MORE INFORMATION

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 Range

In 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 Inclusion

First, 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 Output

You can choose one of two types of output from the Conditional Sum Wizard:

• Copy just the formula to a single cell. This is selected by default.
  
  -or-
• Copy the formula and conditional values.
  
  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: Identifying the Output Cells

The information needed in this step depends on which option you choose in step 3. Perform one of the following steps:

• If you chose the first option (that is, you copied only the sum that results from the current parameters), in this step you need to provide the cell address for the result of the conditional sum on the worksheet. You can select the cell with the mouse, or you can type the cell reference.
  
  -or-
• If you chose the second option in step 3 (that is, included current parameters as well as the result in the worksheet), you need to provide the cell references for all condition parameters as well as for the result. You can select the cell with the mouse or you can type the cell reference. You will find one additional step for each condition that you specified in step 2.

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 Load the Conditional Sum Wizard

The 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:

1. On the Tools menu, click Add-ins
2. In the Add-Ins available list, select Conditional Sum Wizard, and then click OK.

For additional information about downloading a file from Online Services, click the article number below to view the article in the Microsoft Knowledge Base:

Example using the Conditional Sum Wizard

1. In a new Microsoft Excel worksheet, insert the following, with the label Region in cell A1:

       Region   Date        Total Sales    Sales Rep
       South    1-Jan-96    $103,476.98    John Smith
       East     1-Jan-96    $57,400.35     John Smith
       North    5-Jan-96    $95,375.34     Mary Jones
       East     9-Jan-96    $143,564.6     John Smith
       West     9-Jan-96    $200,359.85    Mary Jones
       North    9-Jan-96    $204,014.46    Mary Jones
       South    9-Jan-96    $98,435.12     Mary Jones
       West    13-Jan-96    $65,678.41     John Smith
       West    14-Jan-96    $138,357.60    John Smith
       East    23-Jan-96    $69,189.58     John Smith
       North   23-Jan-96    $40,781.30     Mary Jones
       East    25-Jan-96    $89,053.68     Mary Jones
       North   31-Jan-96    $85,462.12     John Smith
       South   31-Jan-96    $199,980.15    Mary Jones
   					

2. Ensure that the selected cell is within the list, on the Tools menu, point to Wizard, and then click Conditional Sum.
3. In the Step 1 of 4 dialog box, the range

   $A$1:$D$15

   should be identified. If it is not, type the correct range, and then click Next.
4. In the Step 2 of 4 dialog box, in the Column to sum box, select Total Sales.
5. In the Column box, select Sales Rep; in the Is box, select =; in the This value box, select Mary Jones; and then click Add Condition.
6. In the Column box, select Date; in the Is box, select >; in the This value box, type 10-Jan-96; and then click Add Condition.
7. Click Next.
8. In the Step 3 of 4 dialog box, select Copy just the formula to a single cell. Notice that the result shows $329,815.13, and then click Next.
9. In the Step 4 of 4 dialog box, type the cell address F1, and then click Finish.

Known issues that may occur when you use the Conditional Sum Wizard

If 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:

• You try to copy both the criteria and the formula to another sheet in the workbook.
• You apply several conditions.

When Excel builds the string for the formula array, Excel includes the workbook name with every criteria reference. The string may exceed the 255 character limit for FormulaArray method. Even though the workbook names are removed when the formula is entered, the method does not succeed because the character limit was exceeded.