# Simple Expense Tracking With New Excel 2007 Functions

Today’s author, Monica Poinescu, a Software Developer in Test on the Excel team, gives an overview of some new functions in Excel 2007 and along the way shows us how to do some simple expense tracking.

Considering the current economic environment, one may desire to better keep track of personal expenses. The new functions introduced in Office Excel 2007 simplify this effort. Here is how: given a list of expenses sorted by dates and categories (food, travel, clothing, etc.), how do I find how much was spent for each category, per month? And what’s the average amount spent? The functions SUMIFS, AVERAGEIFS and COUNTIFS perform selective calculations: they take into account only those values which fulfill several criteria. The values could be in a range spanning several rows and columns.

Let’s assume I entered the expenses in a table:

The solution below uses the new Excel 2007 feature structured references; see 'Tables Part 3: Using Formulas with Tables' for more details on how to simplify your formulas based on tables.

I’m also using the function SUMIFS which extends the functionality of SUMIF: given a range to be summed, it will add only those values that fulfill all the given criteria (one could use between 1 and 127 criteria applied to corresponding ranges).

The syntax is SUMIFS( the_range_to_sum, range_to_apply_criteria1, criteria1, [range_to_apply_criteria2, criteria2], … ) where the first parameter tells Excel what range we want to sum and the following pairs of arguments tell Excel on which range to apply the respective condition.

In visual terms, I think of each pair (range_to_apply_criteria1, criteria1) as a punch card
with some (0 or more) perforations, in the places where the criteria is TRUE. Stack all these cards on top of each other, the_range_to_sum being on the bottom, and SUMIFS adds only the values that remain visible.

Then start building your reports table by entering in F1:I3 the categories and the months:

and in G2 enter:

=SUMIFS( Table1[Amount], Table1[Category], G\$1, Table1[Date], \$F2 )

which will evaluate the ‘home’ expenses for January (235.35).

It should look like this:

Similarly, in H2 enter:

=SUMIFS( Table1[Amount], Table1[Category], H\$1, Table1[Date], \$F2 )

this will evaluate the ‘food’ expenses for January .

And in I2 enter:

=SUMIFS( Table1[Amount], Table1[Category], I\$1, Table1[Date], \$F2 )

to get your travel related amount.

You can copy/paste these formulas down as you add more months. This will automatically take into account new entries in your expense list. Also, in case you need to figure the average expense per category, within each month, you can use:

=AVERAGEIFS( Table1[Amount], Table1[Category], "home", Table1[Date], "January" ) - this returns 117.675

• Why not use Pivot Table instead?

• Exactly, why not use pivot tables? I'm missing the point here, has anyone found a compelling case to use Excel 2007 tables at all???

• Exactly, why not use pivot tables? I'm missing the point here, has anyone found a compelling case to use Excel 2007 tables at all???

• Tales and these functions are my favorite new features in 2007. Using SUMIFS() on a Table you can easily parameterize conditions and work with multidimensional data more easily than with GETPIVOTDATA() and much faster than with array formulas. Plus the Table is dynamic, and unlike a dynamic range, does not rely on a volatile function. The structured references are very readable, and IntelliSense makes them very quick and error resistant to write.

In this simple example, sure, a PivotTable would be just as good or better, but not always.

• @Jonah -

Are SUMIFS formulas referring to tables faster than equivalent SUMPRODUCT formulas?

G2:

=SUMPRODUCT(Table1[Amount],

(Table1[Category]=G\$1)*(Table1[Date]=\$F2))

? And how are SUMIFS etc useful when the condition is OR? E.g., add a few more entries under Category and consider summarizing under Necessary (rent, utilities) and discretionary (movies, books). Simple enough with SUMPRODUCT.

=SUMPRODUCT(Table1[Amount],

*(Table1[Category]={"movie","books"})

*(Table1[Date]=\$F2))

Yes, I know my DeMorgan's Law,

A or B == not ((not A) and (not B))

so it's possible to contrive SUMIFS formulas as

=SUMIF(Table1[Date],\$F2,Table1[Amount])

-SUMIFS(Table1[Amount],

Table1[Category],"<>movie",

Table1[Category],"<>books",

Table1[Date],\$F2)

but I doubt that'd recalc faster than the equivalent SUMPRODUCT formula.

I have nothing against tables. It's SUMIFS etc that seem pointless to me.

• Carmen, Neal: as is often the case with Excel, there's more than one way to skin a cat.  Pivot Tables are a good alternative approach for this problem.  The point of this blog post was to educate people on some of the new functions in Excel by way of example; it wasn't trying to make a statement about the ideal way to solve this particular problem.  I totally agree though, if you are someone who is very familiar with PivotTables, then that might be a better solution for you.

Though that does give me an idea about a future blog post: pros/cons of pivot-based solutions vs. cell-formula equivalents.

• @Jonah,

Dynamic names need not be volatine

Client =\$a\$1:Index(\$a:\$a,counta(\$a:\$a)) is not volatile unlike the Offset version

Tables in 2007 are not truly dynamic.

Define a table, copy data from another sheet paste it two rows below the last line of the table.

Delete the blank rows. The Table does not expand

@Harlan.

SumIF/s countIF/s are unfortunately much faster than Sumproduct

Data 65536 rows - 3 conditions

Array Entered Sum - 1.13 sec

Sumproduct with Double unary syntax - 1.12 sec

Dsum-Sum - 0.5 Sec

SumIFs - 0.09 sec

Sumifs wins hands down

The only problem is the inability to incorporate an OR logic in SumiF/s

But then its is difficult to do it in Sumproduct as well without array entering it

= Sumproduct((Name="abb")+(City="NewYork"),(Amt)) gives wrong results

TRUE+TRUE = 2 not 1

Sumproduct(IF((Name="abb")+(City="NewYork"),1,0)),(Amt)) - Array Entered works

@Joseph

Pivot Table V/s Formulas.

There are no improvements in Calculated fields / Calculated items in 2007 -Inability to refer to ranges/use Array Formulas in Calculated fields/Items is a major drawback....but this is compensated by the fact that we do have more shades of Blue for formatting the table...no doubt due to the overwhelming demand for it from the customer feedback program...submitted by "real" users

• Using Sumproduct with multiple criteria really isn't difficult.  In the example above, assume Name in column A, City in column B, and Amt in column C.

=SUMPRODUCT((A2:A4="abb")*1,(B2:B4="New York")*1,C2:C4)

This formula gives you the total of the items in column C, where Name is abb, and City is New York.  The "*1" is used with the first two arguments to convert TRUE to 1.

Stan Scott

New York City

• @sam -

Fair point about recalc speed. Wonder whether SIMIFS's speed is due to short circuit boolean evaluation (no need to process additional criteria when an earlier criterion is FALSE).

However, you need to spend more time reading the newsgroups. For mutually exclusive OR (same range equal to one of many alternatives), redundancy works.

=SUMPRODUCT((A1:A100={"A","E","I","O","U"})

*C1:C100)

For different fields, still fairly simple.

=SUMPRODUCT(--((A1:A100="A")+(B1:B100<>"X")>0),

C1:C100)

• @stan,

I was looking to sum column Amt where Name = ABB OR City = New York

@Harlan..

• I for one would like to see the pro/con post. There are probably lots of issues I haven't yet encountered but will (and spend time pulling out my hair, trying to determine why Excel's not working as I expect.) Here are a couple examples:

* Certain chart types (e.g. scattergraph) are not available in pivot tables

* Software that imports Excel files often can't read/instantiate/calculate pivot tables

* Function set very limited in pivot tables (e.g., only average--no median!)

• @Harlan

I never tested SUMPRODUCT() for speed myself, but never had an issue with SUMIFS(). An MSDN article (msdn.microsoft.com/.../aa730921.aspx) on optimizing Excel bottlenecks recommended use in this order: SUMIFS(), SUMPRODUCT(), then array formulas.

@Sam,

Thanks for the detailed test results, and for showing how to write a dynamic range without OFFSET(). I wish that method occurred to me before 2007 came out. I'd been relying on a VBA function to restate ranges as static instead. Even though your example points out how to trick a Table into not dynamically expanding, the Table is dynamic with normal use.

Adding to the topic of Table merit...

The Tables I use are almost always tied to an external data source. It's very helpful to be able to manage these objects differently than Named Ranges.

Calculated Fields in Tables have an important advantage over Calculated Fields in PivotTables; Tables can reference values outside of the object. For example, let's say you have a Table of purchases and corresponding US States. Outside of the Table, you have pairs of US States and their sales tax rate. The Table can have a Calculated Field that references the tax rate. The PivotTable's Calculated Field cannot.

With VBA controlling PivotTables, it's very easy to inadvertently wreck/corrupt your PivotTable, and difficult to detect when this has happened. I haven't encountered any corruption problems with Tables.

If I had to choose only one, I'd take PivotTables, but Tables are an excellent addition to 2007.

• Thank you for these functions.

- In prior xl versions, I could teach all my users SUMIF, but only a fraction of the them would understand SUMPRODUCT to sum with multiple criteria and only that fraction could use it without making a big mess.

- Also I like that SUMIF/SUMIFS can operate on ranges containing Error values. SUMPRODUCT cannot without workaround which makes SUMIF(S) more user friendly for the average user.

• In Table1, I've entered data like this:

a1: monument b1:guide

a2: taj mahal b2: 750

a3: varanasi b3: 102

a4: bodhgaya b4: 50

a5: city palace b5: 300

........

In Table 2, I've created a list to get the data from Table1, ColumnA. So, for example if Table 2 has got data as under:

a2: bodhgaya

a3: taj mahal

a4: left blank

a5: left blank

.

.

.

I need the calculation at a15. Let assume for the above entry, the a15 shows 800 (value of bodhgaya + value of taj mahal)

What formula should I've to enter? Please solve.

Abhay

•              TABLE 1

A         B C

1 Monument Guide

2 Taj Mahal 750

3 Varanasi 102

4 Bodhgaya 50

5 City Palace 300

6 Rajgir        50