VBA How do I replace the range with an array in SUMIF

Question

I have a very simple SUMIF that looks something like this

lngth = Cells(Rows.Count, 9).End(xlUp).Row

For i = 2 To lngth

Range("L" & i & ":L" & lngth) = Application.WorksheetFunction.SumIf(Range("Sheet2!A$2:A$1000"), Cells(i, 9), Range("H$2:H$1000"))
Next i

Sadly, I no longer have access to the data range. But now, the data is shown in a variety of ways. How would I modify the SUMIF so that an array was used in place of the ranges?

Answer 1

You can't, in my opinion. When you pass an array into Sumif when it is expecting a range, it returns a type mismatch. You can try using this Sumif substitute that I made.

Testing requires the range A1:B6 to look like this.

A	B
a	1
a	1
b	1
c	1
d	1
a	1

Code:

Sub test()
    Dim arr() As Variant
    arr = Range("A1:B6")
    Debug.Print mySumif(arr, "a", 1, 2)
End Sub

Function mySumif(ByVal arr As Variant, ByVal criteria As Variant, ByVal criteriaColNo As Integer, ByVal sumColNo As Integer) As Double
    For i = LBound(arr) To UBound(arr)
        If arr(i, criteriaColNo) = criteria Then mySumif = mySumif + arr(i, sumColNo)
    Next i
End Function

Output:

3