How to sum charges to each account made thru year on a monthly basis by account-Excel 2010

I enter the date, account and amount of purchases/charges made in columns A, B & C thru the year on individual rows in an Excel 2010 worksheet.

I now need to sum the charges made thru the fiscal year to each account (entered on one worksheet) on a monthly basis (in columns) by account (in individual rows) (in another file or worksheet) to track the budget balance for each account as the year progresses.

A sample of the two worksheets (expenditures and budget balances) are attached.

If possible, help before tomorrow would be greatly appreciated!

Thank you!

Hi,

See attached

Hi,

Did you add the date 1st January 2012 in D6 on the Budget Balance sheet? i.e. 1/1/2012, and then in E6 and F6 1/2/2012 and 1/3/2012 (or probably since you're based in the US and have your own peculiar date standards 2/1/2012 & 3/1/2012)

Regards

Hi,

I can't see where you have either added the Excel date numbers that I showed (instead of your simple text strings for the month names), nor indeed the formula I gave you.

Your workbook also seems to have changed its layout since the one you first posted. Hence the cell references in the formula I gave you won't of course work and the cell refs will need adjusting accordingly.

If after making the changes and adding the (revised) formula you still can't get it to work then upload the workbook again.

Regards

Hi,

See the attached.

Since you are using row 3 for the months you may as well enter proper date numbers and format them to display months.

I've also added a pivot table summary which you may find just as useful and perhaps more flexible. Particularly if you were to add a column for the beginning budget to your basic data so that you could include this in the Pivot table.

Hi,

In essence Pivot tables are a way of analysing and summarising a two dimensional table of data and reporting totals & counts for combinations of all the (usually numeric but not exclusively) fields in your data.

You can create them in seconds, which makes them very efficient and powerful, and you can learn the basics of them in probably half an hour or so depending on how switched on and comfortable you are with moving around the Excel application.

If you google Excel pivot tables you'll find a wealth of links and youtube videos. In the end the more you experiment and play around the more you'll get out of the subject.

Regards