Conditional Sum Wizard

Removed in 2010
In 2010 the same functionality can be obtained using the SUMIFS worksheet function.
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.

alt text

Add-in Location

Excel 2007 - C:\Program Files\Microsoft Office\Office12\Library\SUMIF.XLA
Excel 2003 - C:\Program Files\Microsoft Office\Office11\Library\SUMIF.XLA
Excel 2002 - C:\Program Files\Microsoft Office\Office10\Library\SUMIF.XLA


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.

alt text

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

alt text

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

alt text

Wizard Step 2 - Add Conditions

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

alt text

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.


Wizard Step 3 - Display Result

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

alt text

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.


Wizard Step 4 - Location

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

alt text

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.


Important

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.


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext