Excel: the Conditional Sum Wizard and the SUMIFS function.

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 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, 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 [e-mail],

Reprinted with permission from AccountingWeb.com.