Excel: the Conditional Sum Wizard and the SUMIFS function.(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:

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, and then select each criteria range and the related criteria.

[ILLUSTRATION OMITTED]

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

[ILLUSTRATION OMITTED]

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.

GET MORE EXCEL TIPS AND TRICKS FROM CHRIS AT THE FOLLOWING OSCPA EVENTS:

* Ohio Accounting Show in Cleveland, Sept. 23-24, course 07701CL

* Ohio Accounting in Columbus, Oct. 28-29, course 04351CO

Chris Wood is controller with The Muncy Corporation and a popular instructor on Excel. You can reach him at cwood15@ohiocpa.net,

Reprinted with permission from AccountingWeb.com.

Print Get the Mag Weekly Updates