Using The Conditional Sum Wizard
The Conditional Sum Wizard allows you to create totals for cell ranges based on multiple criteria.
The SUMIF Function – Single Criteria Only
In Excel, the SUMIF function adds up all the numbers in a range of cells, based on a given criteria. The screenshot is a sample spreadsheet that records details of training courses. Cells G2 and G3 (1) use the SUMIF function to add up the number of attendees for each training location:
G2 contains the function =SUMIF($B$2:$B$9,"Manchester",$D$2:$D$9)
G3 contains the function =SUMIF($B$2:$B$9,"Glasgow",$D$2:$D$9)
In other words, add up the figures in D2:D9 (2) where the value in B2:B9 (3) is Manchester (G2) and add up the figures in D2:D9 where the value in B2:B9 is Glasgow (G3)
Using Multiple Criteria
However, there are many times that it becomes necessary to add up figures based on multiple criteria but the SUMIF function can only check to see if the specified cells meet one condition.
Consider the example in the image:
Cells G2:I3 need to contain the number of attendees for each training location for each month. Although it is possible to manually enter a function, it is far easier to use the Conditional Sum Wizard. This Wizard helps you calculate the sum of values that meet specified conditions (it can even be used as an alternative to SUMIF where you only have a single criteria).
Loading the Add-In
The Conditional Sum Wizard is not loaded by default. On the Tools menu, if Wizard is not present, or if it is, when you point to it, Conditional Sum is not listed, the add-in has not been loaded.
To load the Conditional Sum Wizard, select Tools > Add-ins, tick Conditional Sum Wizard and click OK.
Defining the Range
Select one cell in the range that contains the data to be used for the conditional sum.
Click the Tools menu and select Wizard and then select Conditional Sum.
In the first 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.
Once the range is selected click Next
Defining the Conditions
In step 2, you need to select the column containing the values to sum if the specified conditions are met – click the drop-down arrow (1) and select the appropriate column heading.
Secondly, 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 (2) and operators, and you can either select the value for the condition (3) 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 (4) to add it to the list of conditions (5). If you make a mistake or if you need to change a condition, click Delete Condition to revise the list.
Once all conditions have been set up, click Next
Defining the Output
In step 3, you can choose one of two types of output:
a) Copy just the formula to a single cell
b) 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.
In both cases, the result of the function, using the current settings, is displayed (1)
Once you have selected the appropriate option, click Next
Defining the Result Cell
The information needed in step 4 depends on which option you choose in step 3. If you chose the first option 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.However, if you chose the second option in step 3 you need to provide the cell references for all condition parameters as well as for the result.
Once you have selected the appropriate option(s), click Finish.
Copying the Formula
In the example below, the formula entered in G2 is {=SUM(IF($B$2:$B$9="Manchester",IF($C$2:$C$9="Jan",$D$2:$D$9,0),0))}
Note the curly braces at the start and end of the function. This indicates that the function is an Array Function. If you were typing this manually, instead of pressing ENTER to place the function in a cell, you must press CTRL + SHIFT + ENTER. You can not type the Braces {}. They are entered automatically with the above key combination.
To complete the spreadsheet, it is necessary to copy the function to the other cells (H2:I2 and G3:I3) and then amend each function by editing the cell and changing the criteria – remember to use CTRL + SHIFT + ENTER when you have finished editing.
