ExcelBanter - View Single Post

pub

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