Dates to moths and calculating values for their months

[jigsaw2]

I want to put dates to their months for coloumn A and work out the
monthly totals of column B according to month. Not using a pivot
table.

A B
Date Litres
01-Jan-03 4185
05-Jan-03 4132
14-Jan-03 4132
01-Feb-03 4132
01-Mar-03 4616
01-Apr-03 5223

i would like the output to be as follows:

A B
jan-03 feb-03 subtract jan-03/number of days in jan * 1000
feb-03 mar-03 subract feb-03/number of days in feb * 1000


The problem i have is repeating months in cells fill down
A B
jan-03 xxxx
jan-03 xxxx
feb-03 xxxx

Please help or suggest any ideas, i could do this in a pivot table but
i need to automate macros for this.

Thanks
Lai

 

[Paul Corrado]

Lai,

With your data (Date & Litres) in the range D1:E5 and your output in Columns
A&B, put this formula in Column B



=SUMPRODUCT((MONTH($D$1:$D$5)=MONTH(B1))*(YEAR($D$1:$D$5)=YEAR(B1))*($E$1:$E
$5))/DAY(EOMONTH(B1,0))*1000


PC