Excel: the Conditional Sum Wizard and the SUMIFS function.

• Author: Wood, Chris
• Position: Technology - Reprint

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

[FIGURE 1 OMITTED]

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

[FIGURE 2 OMITTED]

Now suppose I want to sum the employees that are direct and coded to COS. The criteria in the function arguments dialog box (FIG. 2) 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."

[FIGURE 3 OMITTED]

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.

[ILLUSTRATION OMITTED]

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:

[ILLUSTRATION OMITTED]

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 shown). 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 Sum Wizard looks like:

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

Notice the brackets at each end of the formula. That makes this formula an array formula. Without getting too involved in array formulas since that is not the purpose of this article, Excel offers the following definition of an array formula:

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