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

Question

Hello I am Tifu, 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

If you are unfamiliar with VBA, I would start off by recording a macro while doing what you want to do by using the Subtotals feature under the Data menu (i.e. through Excel's interface).

Once the macro is recorded, you can look at the VBA code produced, and alter it to suit your needs.

Answer 2

This should be possible to do using regular excel formulas:

• extract the month of the date (some Month-function)

• then do a conditional sum (function sumwhen (?)) like this:

  = SumWhen(A:B, ReferenceDate, B:B) (with ReferenceDate = C2 C3 etc.)

(All I have is a German excel and the function names got translated as well, so my function names may be off. Maybe someone can check against an English excel and update if necessary.)

Answer 3

This should be possible to do using regular excel formulas:

• extract the month of the date (some Month-function)

• then do a conditional sum (function sumwhen (?)) like this:

  = SumWhen(A:B, ReferenceDate, B:B) (with ReferenceDate = C2 C3 etc.)

(All I have is a German excel and the function names got translated as well, so my function names may be off. Maybe someone can check against an English excel and update if necessary.)

Answer 4

    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.

Answer 5

using array functions: C1:

=date(2008,small(month($A$1:$A$10),1),1)

C2:

=date(2008,small(month($A$1:$A$10),2),1)

right click on these cells and format them as mmm/yy

D1:

=sum(if(month($C1)=month($A$1:$A$10),($B$1:$B$10),0))

make sure to press ctrl-shift-enter when done writing this formula. then copy an paste it down as needed.