Leading the way in Microsoft Office Development

What is the Conditional Sum Wizard ?

The Conditional Sum Wizard will help you to calculate the sums of values that meet specified conditions.

It is possible to sum a block of cells based on a single condition using the SUMIF() function.

If you want to sum a block of cells based on more than one condition this can be achieved by using Array Formulas.

The Conditional Sum Wizard creates a formula which allows you to sum a block of cells based on more than one condition.

This is an additional add-in that can be loaded when necessary. It is not loaded by default.

Installing the Add-in

Before you can use this add-in you must install it. This can be done by selecting (Tools > Add-ins).

Select the add-in called Conditional Sum Wizard and press OK to install it.

 (Tools > Add-ins) dialog box

When this add-in is installed an additional "Conditional Sum…" command will be added to the bottom of your Tools drop-down menu.

Step 1 - Data Table

Lets you identify the table of data. This must include all the column and row headings.

Select (Tools > Conditional Sum) to display the Conditional Sum Wizard dialog box.

Step 2 - Add Conditions

Lets you select which conditions you want to use to filter the table of data.

Column to sum - Select the name of the column containing the values you want to sum.

Add Condition - Adds a new condition to the list.

Remove Condition - Removes the currently selected condition.

Step 3 - Display Result

Lets you choose how you want the formula added to the worksheet.

Copy just the formula to a single cell - Copies just the formula to a single cell on the worksheet.

Copy the formula and lookup parameters - Copies the formula as well as the two lookup parameters to the worksheet. This allows you to quickly change the lookup parameters without having to modify the formula.

Step 4 - Location

Lets you select the single cell to identify which cell you want the formula inserted into.

If you choose "copy the formula and parameters" option in step 3 then the two parameters will be added to the next two cells in the same row.

Things to Remember

You cannot choose to enter a new column label and a new row label. You must choose at least one existing value that matches.

Once the wizard has been used to create the array formula you cannot use the Wizard to change the formula at a later date.