VBA cannot find SUMIFS in WorksheetFunction class

[kweaver]

What's wrong here?

    Dim Arg1 As Range 'the range i want to sum
    Dim Arg2 As Range 'criteria range
    Dim Arg3 As Range 'the criteria (range)
    
    Set Arg1 = Sheets("TextFile").Range("M5:N10000")
    Set Arg2 = Sheets("TextFile").Range("K5:K10000")
    Set Arg3 = Sheets("TextFile").Range("H5:H10000")
    
' get the sum of the drive time hours
For i = 5 To 8
Sheets("TextFile").Cells(i, 21) = Application.WorksheetFunction.SumIfs(Arg1, Arg2, Cells(i, 20).Value, Arg3, "Drive Time")
Next i

 

[kweaver]

I suppose I have to separate the range for Arg1 into two separate ranges??

 

[MARK858]

What happens with

For i = 5 To 8
Sheets("TextFile").Cells(i, 21) = Application.WorksheetFunction.SumIfs(Arg2, Cells(i, 20).Value, Arg3, "Drive Time",Arg1)
Next i

 

[kweaver]

I haven't tried that, but don't think it will work because the 2nd element of the SUMIFS function needs to be a range which is used to match the third element.

 

[DanteAmor]

The sum is correct, but the range of the sum says "N" must be "M"

Set Arg1 = Sheets("TextFile").Range("M5:M10000")

 

[DanteAmor]

Sorry for that, now I understand that you have 2 columns.
It may be so:

Sub test()
  Dim Arg1 As Range 'the range i want to sum
  Dim Arg2 As Range 'criteria range
  Dim Arg3 As Range 'the criteria (range)
  
  Set Arg1 = Sheets("TextFile").Range("M5:M10000")
  Set Arg2 = Sheets("TextFile").Range("K5:K10000")
  Set Arg3 = Sheets("TextFile").Range("H5:H10000")
    
  ' get the sum of the drive time hours
  For i = 5 To 8
    Sheets("TextFile").Cells(i, 21) = Application.WorksheetFunction.SumIfs(Arg1, Arg2, Cells(i, 20).Value, Arg3, "Drive Time") + _
        Application.WorksheetFunction.SumIfs(Arg1.Offset(, 1), Arg2, Cells(i, 20).Value, Arg3, "Drive Time")
  Next i
End Sub

 

[kweaver]

Thanks. That's what I concluded in my post #2 and already incorporated it into the code. Thanks for confirming!!

 

[DanteAmor]

Well, with my example you don't need 2 ranges.
But I'm glad to know that you already solved it.