SumProduct formula Error 13 Type mismatch

Question

I m trying to get the sum with multiple criteria:

• "Get" (Column A)
• "Yes" (Column C)
• Sum Column K

As you can see from the image below i manage to get the answer with SumProduct in excel. When i try to get the same answer with SumProduct using VBA i m receveing an error 13 Type Mismatch.

Image:

Any advice?

Code:

Option Explicit

Sub test()

    Dim Criteria1 As String, Criteria2 As String
    Dim Lastrow As Long, Result As Double
    Dim rng1 As Range, rng2 As Range, rng3 As Range

    With ThisWorkbook.Worksheets("Sheet1")

        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

        Set rng1 = .Range("A3:A" & Lastrow)
        Set rng2 = .Range("C3:C" & Lastrow)
        Set rng3 = .Range("K3:K" & Lastrow)

        Criteria1 = "Get"
        Criteria2 = "Yes"

        Result = Application.Evaluate("SumProduct(--(rng1 = Criteria1),--(rng2 = Criteria2),--rng3))")

    End With

End Sub

Answer 1

Three things

1. rng1, rng2, rng3, Criteria1 and Criteria2 are variables. When you put them inside double quotes, they become a string.
2. You have declared result as a Double. Declare it as a Variant. This is causing the mismatch error.
3. The formula you are actually trying is =SUMPRODUCT(--($A$1:$A$3 = "Get"),--($C$1:$C$3 = "Yes"),--$K$1:$K$3). The criteria should have double quotes around it.

Is this what you are trying? (untested)

    Result = Application.Evaluate("SumProduct(--(" & _
                                   rng1.Address & _
                                   " = """ & _
                                   Criteria1 & _
                                   """),--(" & _
                                   rng2.Address & _
                                   " = """ & _
                                   Criteria2 & _
                                   """),--" & _
                                   rng3.Address & _
                                   ")")