Creating a financial summary of monthly expenses in one workbook with multiple worksheets

[RCJSMOM]

I am trying to create an automated summary page to evaluate household expenses, which are recorded individually. In my spreadsheet, I have a tab for each utility (Natural Gas, Electricity, Internet, Water, phone, etc ) For the first sheet, I am trying to have a summary sheet of all these expenses by month. I have had limited success with "=vlookup(a21,electricitydateamt,2)", where a21 is the date. The challenge is coming in two situations: When 2 payments are made in the same month (for example, April 1 and April 30)... and when the bill is every 2 months (water bill).

summary looks like below (Underlined numbers are wrong)

date electricity nat gas water internet TOTAL
jan 2012 25 20 10 30 85
feb 2012 40 28 13 30 111
march 2012 45 38 13 30 121
April 2012 45 35 30 110

Elec sheet shows

payment date payment amount ... and some more specific usage info for charting
Jan 25 2012 25 ....
Feb 25, 2012 40 ....
April 2, 2012 45 ....
April 30, 2012 55

Electricity does not sum 2 pymts made in April. and repeats something in March when nothing paid


Water sheet shows

Payment date payment amount
Jan 15 2012 10
March 15 2012 13

Water is only every other month, so the formula that I've used puts the same amount in 2 months rather than skipping a month as is actual.


I have the dates in the summary as the end of month, which works fine for monthly, single payments.

I am thinking that I may need to break the date column into 2 so I can use a 1st of month and end of month to sum 2 payments in one month or to skip a month where no payment is made.

I've watched countless videos on dates and searches of various kinds. But cannot find an obvious solution.

Any Thoughts? Thanks in advance.

 

[Slape]

I would recommend using a PivotTable because it will summarize your data like you need it. The only way to use it is to put all your raw data sheets on one tab because it has to be one complete block of information.

The other thing you'll need to add is a column to your raw data showing category (water, natural gas, electricity, internet) and the PivotTable will summarize the data based on those categories and you won't have to split up the dates like you thought.

The other nice thing is that depending on what Excel you are using, the Pivot will update automatically when you add a new record to your raw data. You can also double-click on any grand total on the PivotTable and it will show you every transaction that makes up the total.

 

[vcoolio]

Good day RCJSMOM,

Just an idea.

How about changing the format of your entire work book first? Instead of having expense tabs, change these to month tabs (one for each month) plus a "Summary" sheet. Each month will have columns for each expense which can be totalled individually. Each monthly total can be sent to the "Summary" sheet which in turn will show the monthly totals and, of course, the yearly total (just so you can see how scary yearly house hold expenses can be!!). The overall format should be fairly neat and tidy.
All the totalling can be done by using relatively simple formulae.

Again, just an idea. Let me know what you think.

Kind regards,
vcoolio.

 

[vcoolio]

Hello again RCJSMOM,

Further to my last post, I've created a rough draft workbook which you may be interested in having a look at. I think it will do the job for you. I've formulated three month sheets (Jan, Feb, March) and a Summary sheet for now so that you can get an idea of how it works. Each month sheet has an income and expenses section for you to make your entries in and these will be automatically transferred to the Summary sheet. There's plenty of room for you to create any charts that you may need. I've only formulated some of the expenses columns for now. Have a play with the income and expenses in each month and see what you think. Let me know in due course your opinion. The link to the work book is:-


https://www.dropbox.com/s/7drs2zezog5buxz/RCJSMOM.xlsx?dl=0

I hope this helps.

Kind regards,
vcoolio.

 

[RCJSMOM]

Thanks for your ideas. I have downloaded the workbook and am going to play around with that and the single sheet/pivot table idea. Your help is appreciated.