# Group By Sum in Excel

### Question

• 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)

Thursday, November 11, 2010 10:47 PM

• Proposed as answer by Sunday, November 14, 2010 12:29 AM
• Marked as answer by Thursday, November 18, 2010 8:39 AM
Thursday, November 11, 2010 11:37 PM

### All replies

• Proposed as answer by Sunday, November 14, 2010 12:29 AM
• Marked as answer by Thursday, November 18, 2010 8:39 AM
Thursday, November 11, 2010 11:37 PM
• 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.

Good luck.

Friday, November 12, 2010 2:31 PM
• The OP is alluding to 'changes' in the rows; from A to B to C to whatever.
Ryan Shuell
Friday, November 12, 2010 8:04 PM