I have column A, B and C that contain various values. Column D is the Amount. The doc is sorted by A, then B and then C. At every change in A, B and C I need to sum D. This is easy in Access (group By A, B and C and then sum by D) but I haven't figured out how to do it in Excel. This will need to be included in a Macro for user ease. Here is an example of the data:
ACCOUNTSTRING BATCH TRANDATE Amount 11112010888081 AAKAP0047 11/1/2010 (80.00) 11112010888081 AAKAP0047 11/1/2010 10.00 11112010888081 AAKAP0047 11/1/2010 80.00 11112010888081 AAKAP0047 11/1/2010 5.00 11112010888081 AAKAP0047 11/1/2010 130.00 11112010888081 AAKAP0047 11/1/2010 (130.00) 41010600790001 AAKAP0047 11/1/2010 80.00 41010600790001 AAKAP0047 11/1/2010 (80.00) 41010600790001 AAKAP0047 11/1/2010 (130.00) 41010600790001 AAKAP0047 11/1/2010 130.00 41010600888081 AAKAP0047 11/1/2010 (10.00) 41010600888081 AAKAP0047 11/1/2010 (5.00)
Thanks for your help.
Not sure I understand what you mean by 'on any change' (doesn't excel automatically update calculations when you change data values in a sheet?) but I would use the worksheet_change() event.
Private Sub Worksheet_Change(Target As Range) If Not Application.Intersect(Target, Me.Range("A:D")) Is Nothing Then Me.Range("E1").Value = _ Application.WorksheetFunctions.Subtotal(9, Me.Range("D:D")) End If End Sub
This sub checks if changes occured in columns A through C, and if so, puts the subtotal of column D in cell E1.