Subtotal Calculation

[gderricksmith]

Hello all, and thank you in advance for any help given.
I have 300 - 400 row sheets that I get every week that i need to separate chunks of rows (by dates), insert a blank row, and calculate the subtotal.
Below is an example of what the information looks like before it is subtotaled

Date Total
30-Jan 5
30-Jan 10
30-Jan 11
30-Jan 8
30-Jan 20
6-Feb 5
6-Feb 1
6-Feb 2
6-Feb 8
13-Feb 3
13-Feb 1
13-Feb 1
13-Feb 5
20-Feb 5
20-Feb 14
20-Feb 14


And here is what I need it to look like after the macro has been run

Date Total
30-Jan 5
30-Jan 10
30-Jan 11
30-Jan 8
30-Jan 20
54
6-Feb 5
6-Feb 1
6-Feb 2
6-Feb 8
16
13-Feb 3
13-Feb 1
13-Feb 1
13-Feb 5
10
20-Feb 5
20-Feb 14
20-Feb 14
33


I know this doesn't look good with the formatting in this text box, but I can send the file that I'm working on if you need more clarification. The Date is supposed to be Column A and the number beside it is Column B
Please Help!

 

[millhouse123]

Probably the easiest way is to use the year function =year(cell with date) and then use excels subtotal tool under the data tab in.

 

[gderricksmith]

Thanks millhouse. I tried the year function but continue to get "2011" for every date in the sheet. Is there something that I'm missing here? Or is there any way to get the make a macro to auto outline by groups?

Thanks!

 

[anglais428]

If your dates are in column A and totals are in column B, enter in cell C2, =IF(A2=A1,B2+C1,B2) and fill down (you can hide this column), then in D2 enter, =IF(AND(A2=A1,A3=A2),"",IF(AND(A2=A1,A3<>A2),C2,"")) and fill down. This gives the results you are wanting and the other cells will be blank.

 

[millhouse123]

My bad... Instead of the year function try the month function to get the number of the month, then you can sort and use the subtotal in excel. Also make sure your data is formatted as a date so that excel recogizes the data properly.