The Conditional Sum Wizard and the SUMIFS Function

A very useful tool in Excel is the conditional sum wizard. It expands the SUMIF function by allowing for multiple conditions. Let's review. First the basic SUMIF function. I have a table of an employee listing which includes department, classification (direct, indirect or salary) and expense category (SGA or COS).

Suppose I want to sum all of the employees that are just direct. Use the SUMIF function.

Now suppose I want to sum the employees that are direct and coded to COS. The criteria in the function arguments dialog box above only allows for one entry, so the conditional sum wizard needs to be used.

The Conditional Sum launch button needs to be placed in the ribbon first. Click on the office button and then click on "Excel Options" at the bottom of the drop down menu, then select "Add-ins".

Select "Excel Add-ins" in the Manage box and click the "Go" button.

Check the "Conditional Sum Wizard" and any other options that might be of interest. Then click the "OK" button. Now the Conditional Sum button will show in a new group called Solutions under the Formula ribbon tab.

Go to Tools > Add-Ins and click on Conditional Sum Wizard for Excel 03 users.

The Conditional Sum selection will be found under Format in the main menu. Now click on the Conditional Sum button and traverse through the four dialog boxes that follow:

Dialog box steps

• Select the data
• Set the criteria
• Headers or formula result
• Place the result

The sum of all employees from row 5 through row 45 (See step 1 above) is 95 (not showing). The number of direct employees that are recorded in COS is 67 out of the total of 95 (See step 3 above). The actual formula from using the conditional wizard looks like:

{=SUM(IF($B$5:$B$42="Direct",IF($C$5:$C$42="COS",D$5:D$42,0),0))}

Notice the parenthesis at each end of the formula. That makes this formula an array formula. Without getting too involved in array formulas because that is not the purpose of this article, excel help defines an array formula as:

"An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result. For example, you can place an array formula in a range of cells and use the array formula to calculate a column or row of subtotals. You can also place an array formula in a single cell and then calculate a single amount. An array formula that resides in multiple cells is called a multi-cell formula, and an array formula that resides in a single cell is called a single-cell formula."

Excel 07 has a new formula available that performs the same function as the conditional sum wizard. That formula is the SUMIFS formula. The arguments for this formula are to highlight the target range to be summed, then select each criteria range and the related criteria.

Notice that I get the same answer "67" that was returned via the conditional sum wizard. An advantage of the SUMIFS is that wildcard characters the question mark (?) and the asterisk (*) can be used in the criteria. A question mark matches any single character, and an asterisk matches any sequence of characters.

Finally, "IFS" formulas are also available in AVERAGE (AVERAGEIFS) & COUNT (COUNTIFS).
Excel 07 IFS formulas provide ease and versatility to your programming needs ifs you decide to use them.