Sum daily data into monthly data using array formulae

[Red Rum]

Firstly, I have been reading this board for a number or weeks, and have to say I have great admiration for the helpfulness and expertise of many of the contributors. I have so far learned many new tips.

However, i've got a problem I can't quite solve. I've been stuck for the last few hours, and have only half found a solution by some useful advice posted on http://www.cpearson.com/excel/array.htm. but can't get it to work with dates.

The problem...

Users enter trading data on a day by day and trade by trade basis.
I need to summarise this data in another worksheet, but on a monthly basis

The data...

INPUT SHEET
------A------B-------
1 Date , Amount
2 6-Apr-03 , 500
3 6-Apr-03 , 200
4 7-Apr-03 , 100
5 10-May-03 , 300
(dates input as Excel date format) This is only a simple representation - the actual input sheet has many more entries in per month.


OUTPUT SHEET
-------A-------B-----
1 Date , Amount
2 Apr-03 , 800
3 May-03 , 300
4 Jun-03 , -
etc

The array formula I have been trying in B2 of the output sheet is:
{=SUM(input!B2:B1000)*(MONTH(A2)=MONTH(input!A2:A1000))*(YEAR(A2)=YEAR(input!A2:A1000))}

But this returns 1100 in B2, and when copied down to B3, returns zero.

consequently I'm a bit perplexed, and wondered whether any of you could help me.

Any help you can give would be greatly appreciated.

 

[PaddyD]

you're a bit perplexed, but not as much as I am. given you've already posted this question:

http://www.mrexcel.com/board2/viewtopic.php?t=53262

why the duplicate? if you've got more to add to your original thread, simply post a reply to it.

given the duplication, I have locked this thread - PM me if you disagree.

paddy