# SumIf using Excel VBA

You may need to change the references as commented throughout my code, but this sub should do the trick for you. Also, I used sumif formulas, but if your raw dataset ever grows larger, I’d suggest to look into using collections instead as an efficiency measure.

Option Explicit

Sub SumByPK()

Dim dt As Object
Dim lastrow1 As Long
Dim lastrow2 As Long
Dim sh As Worksheet
Dim InputRng As Range
Dim OutRng As Range
Dim rng As Range
Dim qty, JanPrice, FebPrice As Double
Set dt = CreateObject("Scripting.Dictionary")
Set sh = Sheet1 'sheet object, change this if different

lastrow1 = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row 'determines last row, assuming there are no other data in A column but the primary keys.

Set InputRng = sh.Range("A2:A" & lastrow1) 'change the range ref in different
Set OutRng = sh.Range("G2") 'will output unique primary keys onto this range, change this if needed.
For Each rng In InputRng
If rng.Value <> "" Then
dt(rng.Value) = ""
End If
Next rng

OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)

lastrow2 = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row 'determines last row, assuming there are no other date in G column but the unique primary keys.

For Each rng In sh.Range("G2:G" & lastrow2) 'change the range reference if the OutRng has been changed.
If rng.Value <> "" Then
qty = Application.WorksheetFunction.SumIf(sh.Range("A2:A" & lastrow1), rng.Value, sh.Range(Cells(2, 2).Address & ":" & Cells(lastrow1, 2).Address))
JanPrice = Application.WorksheetFunction.SumIf(sh.Range("A2:A" & lastrow1), rng.Value, sh.Range(Cells(2, 3).Address & ":" & Cells(lastrow1, 3).Address))
FebPrice = Application.WorksheetFunction.SumIf(sh.Range("A2:A" & lastrow1), rng.Value, sh.Range(Cells(2, 4).Address & ":" & Cells(lastrow1, 4).Address))
rng.Offset(0, 1).Value = qty
rng.Offset(0, 2).Value = JanPrice
rng.Offset(0, 3).Value = FebPrice
End If
Next rng

End Sub�
�