How to sum data in one column based on the same month in another column using macros

Question

I am using macros in excel 2007 for my work. I am working with many data and I need to sum data from 2 or more rows in the same coloumn according to the same month. However the month column is expressed as date.

for example, i have series of data

A            B
2/10/2008    2
2/10/2008    3
4/10/2008    3
5/11/2008    4
5/11/2008    5

I want the result to be displayed in column C and D as followed

C            D
Oct/08       8
Nov/08       9

I am very thankful if anyone can help me.

regards,

Tifu

Answer 1

    A           B       C   D       E       F
1   10/ 1/2008  24106   1   Oct-08  24106   8
2   10/31/2008  24106   7   Nov-08  24107   11
3   11/ 1/2008  24107   8   Dec-08  24108   6
4   11/30/2008  24107   3               
5   12/ 1/2008  24108   2               
6   12/ 2/2008  24108   4               

B1 =MONTH(A1)+YEAR(A1)*12
E1 =MONTH(D1)+YEAR(D1)*12
F1 =SUMIF(B$1:B$6,CONCATENATE("=",E1),C$1:C$6)

I had to overcome two problems to solve this. First, SUMIF can only do direct comparison, it cannot run a function on the source location (except for range functions, which the date and time functions are not), so I had to add the B column. The E column is optional, it could be implemented as part of the formula in F, but I left it independent for illustrative purposes. Second, SUMIF takes a string parameter describing the comparison, so I built the necessary string (it is "=24106" for F1) on the fly.