VBA alternative to Excel SUMPRODUCT multiple criteria lookup

Question

I have a large spreadsheet with tens of thousands of rows. I want to look up values based on multiple criteria and get the associated values. Currently, I use the SUMPRODUCT function, but with that many rows, it takes many minutes to calculate.

Function:

=SUMPRODUCT((array 1 criteria) * (array2 criteria) * array values) 

Example:

=SUMPRODUCT((B15:B23=”John”)*(C15:C23=”North”)*(E15:E23=1)*D15:D23)

Example from here.

Question: Is there a more efficient way to do this type of lookup with multiple criteria - maybe with VBA? I have tried using index match, but it only gives me the value of the first match and I am not sure it is better performance-wise.

Answer 1

If you dont want to use Pivot try this.. As all the arguments are range, select desire range for entering the input.

Function VBSumProd(nameRng As Range, nameCrt As Range, regionRng As Range, regionCrt As Range, salesRng As Range, qtrRng As Range, qrtCrt As Range) As Double

Dim i As Long, tempSum As Double
tempSum = 0

For i = 1 To nameRng.Rows.Count
    If WorksheetFunction.And(UCase(nameRng(i)) = UCase(nameCrt), UCase(regionRng(i)) = UCase(regionCrt), qtrRng(i) = qrtCrt) Then
    tempSum = tempSum + salesRng(i)
    End If
Next

VBSumProd = tempSum
End Function

You tried code below which is slow compared to subtotal

Function VBSumProd(nameRng As Range, nameCrt As String, regionRng As Range, regionCrt As String, salesRng As Range, qtrRng As Range, qrtCrt)
Dim nameRngArr, regionRngArr, salesRngArr, qtrRngArr
Dim i As Long, tempSum As Double
tempSum = 0

ReDim nameRngArr(nameRng.Rows.Count)
ReDim regionRngArr(nameRng.Rows.Count)
ReDim salesRngArr(nameRng.Rows.Count)
ReDim qtrRngArr(nameRng.Rows.Count)

For i = 1 To nameRng.Rows.Count
    nameRngArr(i) = nameRng(i)
    regionRngArr(i) = regionRng(i)
    salesRngArr(i) = salesRng(i)
    qtrRngArr(i) = qtrRng(i)
Next

For i = 1 To nameRng.Rows.Count
    If WorksheetFunction.And(UCase(nameRngArr(i)) = UCase(nameCrt), UCase(regionRngArr(i)) = UCase(regionCrt), qtrRngArr(i) = qrtCrt) Then
    tempSum = tempSum + salesRngArr(i)
    End If
Next
VBSumProd = tempSum
End Function