Analysing data imported from MS Money
Hi Excel experts,
I'm struggling with something that is probably quite simple. I've
imported a whole load of financial transactions from MS Money. They're
in a worksheet of their own called "Money data". Key data:
Column B has date of transaction
Column F has the amount
Column H has the category into which the transaction falls
Within the same workbook in a separate worksheet called "Annual
figures" I want to have a table of total annual expenditure month-by-
month from each category. Here are the columns I currently have in
that worksheet.
Column A: a list of all the categories
Column B: I want this to contain total expenditure for the relevant
category in the twelve months ending 31st Jan 08
Column C: I want this to contain total expenditure for the relevant
category in the twelve months ending 29th Feb 08
Column D: etc, etc
In row 4 of each column I have as a heading the month to which that
column applies. It's displayed as eg "Jan-08", although the data
within the cell is 01/01/2008 (ie the beginning of the month, not its
end).
I've been playing about with the "sumif" function, but I'm really
struggling. Problems:
- I'm not sure if I'm using 3D cell references correctly
- I'm not sure how to express the first condition - the range of dates
I'm summing within the formula and how to link it to the date in row 4
- Similarly I'm not sure how to have a second condition, ie for the
figures to go into the sum, as well as being in the relevant 12-month
period for that column they must fall into the category for that row.
Any helpful suggestions and pointers gratefully received.
Cheers!
Martin
|