Geek asks and answers

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

I use macros in excel 2007 for my work. I work with a lot of 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 a date.

for example, I have a 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 appear in columns C and D as follows

C            D
Oct/08       8
Nov/08       9

I am very grateful if anyone can help me.

Yours faithfully,

Tifu

+3
source share
4 answers
    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)

, . -, SUMIF , ( , ), B. E , F, . -, SUMIF , , ( "= 24106" F1) .

+4

: 1:

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

2:

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

mmm/yy

D1:

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

ctrl-shift-enter, . .

+1

VBA, , , , Subtotals Data (, Excel).

VBA .

0

excel:

  • ( )

  • ( sumwhen (?)) :

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

(, , - excel, , . , - .)

0

More articles:


All Articles