Personal Expenses Spreadsheet - Stuck with IF Function

[alcatsar]

Hi Folks,

Just want to say I'm so glad I came across this forum. Been searching the internet for days trying to find a tutorial on what I'm trying to achieve. Unfortunately, I've had no success.

I've attached a MS Excel spreadsheet I've created [Filename: 032209_Expense_Report.xlsx].

There are couple of places where I'm stuck and I hope one of you can help:
(the points marked with a * aren't crucial to my requirements)

1. When I add more rows to the table I have to manually select the last cell and pull the formulas down to populate them into the new rows. I know it takes just a little effort but more automation would be great. Not a must but would appreciate some tips and tricks.*

2. To retrieve the Savings amount I have merely referenced the last cell in the Balance column. Just curious if there's a "smarter" way to achieve this.*

3. Day - Is there a way for this column to state the day based on the information from the Date column?*

4. Expense By Type
This is the part I'm really, really stuck at.

Here's an example of what I'd like to do;

The value displayed in cell C19 should be the sum of all "Transportation" expenses available in the table (From column G - Amount). I might have one Transportation expense on 22 Mar, another on 25 Mar, a third on 31 Mar and so and so forth. I'm interested in only the total "Transportation" expenses being displayed in cell C19.

Likewise, the remaining expense types will only show their totals.

I would really appreciate if somebody out there can assist me with this.

[mrice]

Please have a look at the attachment.

I've used a nested INDEX function to return the last row of the balance which will self adjust with the number of rows.

The conditional sum is acheived with the SUMIF function.

A simple referencing solves the weekday question.

[alcatsar]

Thanks but it doesn't quite seem to work the way I was hoping.

See the attached example.

Also Expense By Type does not show the sum of "Transportation" related expenses. It just shows "1". Any clue?

[mrice]

It's not working because there are missing values in the type and balance columns in your example.

Please see the attachment.

[alcatsar]

It's not working because there are missing values in the type and balance columns in your example.

Please see the attachment.

Thank you very much mrice. Sometimes things can be so simple if you just know how to use 'em!

[Ambrosia242]

Hi,

I'm trying to do the same thing but it isn't working for me. Does that function not work if the 'type' is from a drop down list?

[alcatsar]

Hi,

I'm trying to do the same thing but it isn't working for me. Does that function not work if the 'type' is from a drop down list?

I've tried it with a drop-down list in Excel 2007 and it appears to function.

I have another question though. How do I obtain a percentage based on tabulated results over the expenses budget (i.e. Transportation/$630 x 100%)?