Normally, one might use a filter to do this or maybe an =SumProduct() method.
I did all but one part. You can either write another Sub to update for all Data!C values, or use the Worksheet event method and pass Target as the range to the PutOff2RSum() routine.
These would be in a Module.
Code:
Sub Test_Off2Rsum()
MsgBox Off2RSum(Worksheets("Data").Range("C3"))
End Sub
Sub Test2_Off2Rsum()
Dim c As Range
For Each c In Worksheets("Data").Range("C2", Worksheets("Data").Range("C" & Rows.Count).End(xlUp))
MsgBox c.Address & ": " & CStr(Off2RSum(c))
Next c
End Sub
Sub Test_PutOff2Sum()
PutOff2RSum Worksheets("Data").Range("C3")
End Sub
Sub PutOff2RSum(aRange As Range)
With aRange
Worksheets(.Offset(, -2).Value2).Range(.Offset(, -1).Value2) = Off2RSum(aRange)
End With
End Sub
Function Off2RSum(aRange As Range) As Double
Dim c As Range, ws As Worksheet, d As Double
Set ws = Worksheets(aRange.Parent.Name)
For Each c In ws.Range(ws.Cells(2, aRange.Column), ws.Cells(Rows.Count, aRange.Column).End(xlUp))
With aRange
If c.Offset(, -1).Value2 = .Offset(, -1).Value2 And _
c.Offset(, -2).Value2 = .Offset(, -2).Value2 Then _
d = d + c.Value
End With
Next c
Off2RSum = d
End Function
Bookmarks