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
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…