Dates to months and calculating values for their months
 

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

Dates to months and calculating values for their months
 

=Sumif(A:A,"=01-Jan-03",B:B)-Sumif(A:A,"=01-Feb-03",B:B)

will get the sum for the month of Jan

If you want the count of rows with a Jan-03 entry

=COUNTIF(A:A,"=01-Jan-03")-COUNTIF(A:A,"=01-Feb-03")

You can use these same formulas in code (demonstrated from the immediate
window:)

? Application.Sumif(Columns(1),"=01-Jan-03",Columns(2))- _
Application.Sumif(Columns(1),"=01-Feb-03",Columns(2))
12449

Hopefully this is something you can use. I can't say I totally understand
your question.

--
Regards,
Tom Ogilvy


"jigsaw2" wrote in message
...
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