VBA Excel Sumif and sumifs function

Question

Sales Details

Month	Amount
January	120
March	250
April	380
June	510
January	640
March	770
April	900
June	1030
January	1160
March	1290
April	1420
January	1550

Noted: all vba code write below.........

Sub sum_if_Function()

Dim i As Long
Dim j As Long

'Note:rng means range**
Dim rng1(4 To 15) As Variant
Dim rng2(4 To 15) As Long
Dim Result As Long

'Note: This below code write for store all Month Name**
For i = 4 To 15
  rng1(i) = Cells(i, 3).Value
Next i

'Note: This below code write for store all Each Month Sale Amount**
For j = 4 To 15
  rng2(j) = Cells(i, 4).Value
Next j

'Note: This below code write for display January month total sales**
For i = 4 To 15
  Result = Application.WorksheetFunction.SumIf(rng1(i), "January", rng2(i))
Next i

MsgBox "January Month Total Sales Amount:-" & Result

End Sub

this is my VBA code where i am trying to get January month total sales but when i run this code then vba excel compile display as massage ...run time error "424" object required

I am also tried to use two dimension array for get this result but i am failed

i need good and easy code using array so that i can get the result and also need suggestion

Answer 1

You have to pass a ranges to SumIf - not values in an array!

Try this:

Sub sum_if_Function()
Dim rgMonths As Range
Set rgMonths = ActiveSheet.Range("C4:C15")

Dim rgAmount As Range
Set rgAmount = ActiveSheet.Range("D4:D15")

Dim result As Single
result = Application.WorksheetFunction.sumIF(rgMonths, "January", rgAmount)

MsgBox "January Month Total Sales Amount:-" & result
End Sub

Answer 2

If your original data is in a sheet, the preferred version is to use SumIF function as proposed by @Ike.
But if your original data are not from the sheet you can adopt another variant like below (I have used a template from @Ike's answer).

Sub Sum_by_months()
    Dim rgMonths As Variant
    rgMonths = ActiveSheet.Range("C4:C15").Value

    Dim rgAmount As Variant
    rgAmount = ActiveSheet.Range("D4:D15").Value

    Dim result As Variant
    With Application  ' not .WorksheetFunction
        result = .SumProduct(.IfError(.Match(rgMonths, Array("January"), 0), 0), rgAmount)
    End With
    MsgBox "January Month Total Sales Amount: " & result
End Sub

Be careful. Don't use WorksheetFunction object, the name of the month must be an array.