Try this UDF.
=sumOfMonth(myDate) can be used in a spreadsheet formula.
If the value in column C is a date in the same month as myDate then the values in columns A and B on that row are added into sumOfMonth.
You may need to change the name of the sheet where you keep your data.
Function sumOfMonth(ByVal myDate As Double) As Double
Dim dataRange As Range, cel As Range, celVal As Variant
With Sheets("sheet1")
Set dataRange = Range(.Cells(.Cells.Rows.Count, 1).End(xlUp), .Cells(1, 1))
End With
For Each cel In dataRange
celVal = cel.Offset(0, 2).Value
If Year(myDate) = Year(celVal) _
And Month(myDate) = Month(celVal) Then
sumOfMonth = sumOfMonth + Val(cel) + Val(cel.Offset(0, 1))
End If
Next cel
End Function
I'm not sure, but I'll betcha that the SUMPRODUCT is faster than this.
Bookmarks