##### Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

# sum amounts based on column

0

Hi,

I need help to sum amounts in column F for Duplicates ID in column A .

the result should show ID  in column J and amounts in column K

I tried to write simple macro but doesn't give me what I want!

``````Sub sumup()
Dim i As Long
Dim lr As Long
Dim sh As Worksheet
Set sh = Sheets("SSS")
lr = sh.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
If Cells(i, 1) > 1 Then
Range("J1:K1") = Array("ID", "AMOUNT")
Cells(i + 1, 10) = Cells(i, 1)
Cells(i + 1, 11) = Cells(i, 6)
End If
Next
End Sub
``````

0

Hi again Speed.

I think I know what you mean...

In the attached revised file, I've added a new variable SubT to collect the subtotal value for a given Customer # (ID) and changed the code (see bits in bold, including comments which you can delete):

``````Sub sumup()
Dim i As Long
Dim lr As Long
Dim sh As Worksheet
Dim SubT As Double

Set sh = Sheets("SSS")
lr = sh.Range("A" & Rows.Count).End(xlUp).Row
' clear any values
Range("J:K") = ""

For i = 2 To lr
' compare column A values
If Cells(i, 1).Value = Cells(i + 1, 1).Value Then
SubT = SubT + Cells(i, 6)

Else
' output subtotal for Customer# and reset
Range("J1:K1") = Array("Customer #", "Total")
Cells(i, 10) = Cells(i, 1)
Cells(i, 11) = SubT + Cells(i, 6)
SubT = 0
End If
Next i
' resize columns
Range("J:K").Columns.AutoFit

End Sub``````

Now the subtotals will appear on the last row for a given Customer #. (Other users should note that the column order is  right to left, not the left to right normally used in the Europe and the USA for example).