Analysing data imported from MS Money
On 1 Aug, 06:01, pub wrote:
wrote in news:d3565fa3-e92a-4636-a8e6-
:
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
1st some assumptions
- i assumed your Money data sheet starts in Row 1 and ends in row 29
- Annual Figures cell A5 is categ1
- Annual figures cell B4 is 01/01/2008
then you can use the sumproduct formula in cell B5
=SUMPRODUCT(('Money data'!$B$1:$B$29=B$4)*('Money data'!$B$1:$B$29<=DATE
(YEAR(B$4),MONTH(B$4)+1,1))*('Money data'!$H$1:$H$29=$A5)*('Money data'!$F
$1:$F$29))
if its not working
if you get an #N/A.
i randomly assumed 29 rows. *remember all your ranges have to be the same
number of rows.
each section of the sumproduct should work independantly and give you a
proper count.
so a count of the categories (the 3rd section of the formula) would be
=SUMPRODUCT(('Money data'!$H$1:$H$29=$A5)*1)
the 4th section does the sum
if you get it working on a smaller sheet, so you can easily count and
change to test, then it should be easy to apply to your full sheet.
good luck
Hi Pub,
It's taken me a couple of weeks to get round to implementing this, but
just wanted to say a belated thanks for your suggestion, which I've
got to work fine. Thanks for putting me onto the sumproduct function,
which is ideal.
One observation: the logical arguments don't work very well. For
example the ('Money data'!$B$1:$B$29=B$4) bit produces 'FALSE' or
'TRUE' rather then 0 or 1, which screws the whole thing up. I had to
coerce excel into giving me 1 or 0 by doing (0+('Money data'!$B$1:$B
$29=B$4)) which seems to work.
Also, I had to replace the asterisk characters with commas.
Frankly I'm out on a limb here, knowing very little about Excel apart
from absolute basics, so if the above is rubbish do please tell me,
but it seems to work.
Cheers!
Martin
|