VBA SUMIFS - Not Working, All Inputs Return Correct Values

Question

The following code is throwing an 1004 runtime error when I go to run. The debugger highlights the SUMIFS function in just above part 2.

The goal of this sub is to locate duplicate rows using columns 1, 3, and 5 as "primary-keys" and then combine values by column for rows 6-7 and 10-17 based off those values for each row. Hence the SUMIFS.

I'm confused. Using the immediate window I can return the correct values for each section of the SUMIFS fuction (ws.Cells(lRow, lCol).Select will select the correct cell, etc.). My next thought was that the ranges were not interpreted correctly by the SUMIFS so I popped the same function, using specific ranges, into the immediate window and received the same error. See line below for immediate window entry - note that the goal is to combine values between rows 21:23 in this example.

debug.Print application.WorksheetFunction.SumIfs(range("F21:F23"), range("A9:A30"), range("A21").Value, range("C9:C30"), range("C21").Value, range("E9:E30"), range("E21").Value)

I'm assuming, and 100% sure, that named ranges, .codenames, and variables are working as desired. That said, I've been mistaken before.

Any help would be greatly appreciated.

Private Sub dba_combine_rows()

Const COL_TRIPS = 6
Const COL_EMP_TRIP = 7
Const COL_LN_HC = 10
Const COL_USN_PR = 17
Dim lLastRow As Long

Dim ws As Worksheet
Set ws = DBA

Dim answer As Integer
answer = MsgBox("Are you sure you want to combine rows?", vbYesNo, "Combine Rows")
If answer = vbNo Then
    Exit Sub
End If

'Get the last row
    Dim i As Long
    For i = Range("inputRange" & ws.CodeName).Column To (Range("inputRange" & ws.CodeName).Column + Range("inputRange" & ws.CodeName).Columns.Count - 1)
        If ws.Cells(ws.Rows.Count, i).End(xlUp).Row > lLastRow Then
            lLastRow = ws.Cells(ws.Rows.Count, i).End(xlUp).Row
        End If
    Next i

''Combine, start modify

    'Set aliases for columns A & B & C, used for checking duplicates
    Dim rngA As Range, rngB As Range, rngC As Range
    Set rngA = ws.Range("inputRange" & ws.CodeName).Columns(1)
    Set rngB = ws.Range("inputRange" & ws.CodeName).Columns(3)
    Set rngC = ws.Range("inputRange" & ws.CodeName).Columns(5)

    Dim lRow As Long, lCol As Long, strHolderA As String, lHolderR As Long
    For lRow = ws.Range("inputRange" & ws.CodeName).Row To lLastRow

    'Part 1 - Check for duplicate entity-country
        If Application.CountIfs(rngA, ws.Cells(lRow, rngA.Column), rngB, ws.Cells(lRow, rngB.Column), rngC, ws.Cells(lRow, rngC.Column)) > 1 Then

        strHolderA = (ws.Cells(lRow, rngA.Column).Value & ws.Cells(lRow, rngB.Column).Value & ws.Cells(lRow, rngC.Column).Value)
        lHolderR = lRow

            For lCol = COL_TRIPS To COL_USN_PR
                If lCol = COL_EMP_TRIP Then
                    lCol = COL_LN_HC
                End If
                ws.Cells(lRow, lCol).Value = Application.WorksheetFunction.SumIfs( _
                    ws.Range(Col_Letter(lCol) & lRow & ":" & Col_Letter(lCol) & lLastRow), rngA, ws.Cells(lRow, rngA.Column).Value, rngB, ws.Cells(lRow, rngB.Column).Value, rngC, ws.Cells(lRow, rngC.Column).Value)
            Next lCol

        'Part 2 - Delete similar rows, excluding 1st
            Dim lRow2 As Long
            For lRow2 = ws.Range("inputRange" & ws.CodeName).Row To lLastRow

                If (ws.Cells(lRow2, rngA.Column).Value & ws.Cells(lRow2, rngB.Column).Value & _
                        ws.Cells(lRow2, rngC.Column).Value) = strHolderA And lRow2 <> lHolderR Then
                    Rows(lRow2 & ":" & lRow2).Select
                    Selection.Delete Shift:=xlUp
                    lRow2 = lRow2 - 1
                End If
            Next lRow2

        End If

    Next lRow

End Sub

Answer 1

The sum_range parameter (the first one) has to be the same size as the criteria ranges, which all. also, have to be the same size.

"F21:F23" is just 3 cells, while the others i.e. "A9:A30" count 22 cells each.