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
