Excel, rules: Tables, conditional summing are two Excel 2007 enhancements you're sure to love.

• Author: Lenning, Jeff

Two enhancements introduced in Excel 2007 may have slipped past you: multiple condition sum with SUMIFS and Tables (not Pivot Tables). It's my hunch that once you discover them, you'll fall in love with them the way I have.

Multiple Condition Sum

The SUM() function, which adds all cells in a range, is probably your most favorite and most used function. Sometimes, however, we only want to include rows that satisfy a condition. For example, only those rows where account equals "cash."

Historically, performing a conditional sum that has only one condition was easily accomplished with the SUMIF() function. But that function does not allow multiple conditions, such as including those rows where account equals "cash" and where period is equal to "January."

In versions of Excel prior to 2007, creating a multiple condition sum requires the use of complicated functions, like SUMPRODUCT() or Array formulas. However, Microsoft introduced the SUMIFS() function in Excel 2007, which supports multiple conditions. I'll demonstrate one very cool use of this function.

Let's assume you have a client that uses QuickBooks and you need a report that QuickBooks doesn't provide exactly right. Let's also assume you would like an efficient way to export transaction data from QuickBooks and have Excel formulas automatically pull the right values to the right cells. This is now easy, thanks to the SUMIFS() function.

For this example, we have a workbook called report.xlsx (if you'd like to play along, you can download this workbook from www.clickconsulting.com/tbrg2010).

The workbook contains a worksheet named QBData, which represents the "landing page" that accepts direct QuickBooks exports. QuickBooks supports the export of virtually any report or transaction list into an Excel format, but the best trick is to export a QuickBooks report to an existing workbook and to a specific worksheet within the workbook (Figure 1).

[FIGURE 1 OMITTED]

This technique makes recurring processes more efficient. Once you export the QuickBooks data into the QBData worksheet each period, the smart formulas on the report worksheet will automatically pull the correct values from the QBData sheet. That brings us to a smart formula, the SUMIFS() function. The syntax of the SUMIFS() function follows: =SUMIFS(sum_range,criteria_range 1,criteria 1, ... ), where:

* sum_range is the range of cells that you want to add.

* criteria_range 1 is the range of cells that has the values to compare to...