"Living the Dream" wrote:
I'm still curious as to why MS included the (.)Sumproduct
feature as selectable after WorksheetFunction if it is not
executable considering it would have made life so much easier

You have a fundamental misunderstanding of who is providing which feature.

The general form of Excel SUMPRODUCT is:

SUMPRODUCT(array1, array2,....)

and that is exactly what WorksheetFunction.SumProduct implements in VBA.

When we write an Excel expression of the form:

SUMPRODUCT(($A$2:$A$2500=H17)*($B$2:$B$25001)*($F $2:$F$25000))

Excel constructs 3 arrays of logic values, combines those arrays by
multiplying element-by-element, and finally passes a single array to
SUMPRODUCT.

The point is: the implicit construction of arrays from expressions is an
Excel feature.

VBA does not have that sophisticated feature of implicitly constructing
arrays from expressions in that manner.

Instead, we must construct the arrays explicitly, if we do not want to rely
on the VBA Evaluate function, which relies on Excel.

And by the way, that gives rise to a much more efficient evaluation in some
cases.

For example, in your code snippet, the conceptual arrays (mySumB = myMth)
and (mySumF 0) are invariant with respect to the loop. That is, they only
need to be evaluated one time outside the loop.

So you might write:

n = UBound(mySumB,1) ' mySumA, mySumB and mySumF must be same size
ReDim a1(1 to n) As Byte
ReDim a2(1 to n) As Byte
For i = 1 to n
' True is -1 in VBA, not 1 as in Excel
a1(i) = -(mySumB(i) = myMth)
a2(i) = -(mySumF(i) 0)
Next

ReDim a3(1 to n) as Byte
For Each c1 in Anchor1
If c1 < "" Then
For i = 1 to n
a3(i) = -(mySumA(i) = c1)
Next
c1.Offset(0,1) = WorksheetFunction.SumProduct(a1,a2,a3)
End If
Next

"Inconvenient"? Yes. But it is a limitation of the VBA language, not the
WorksheetFunction.SumProduct implementation.

------

PS: Unless you have other needs for the range variables, it would be much
more efficient if mySumA et al were variant arrays, not range variables. To
wit:

mySumA = Ssht.Range("$A$2:$A$2500")

Thus, you are accessing the range (and Excel) only once for each range.

Then you would reference mySumA(i,1) instead of mySumA(i).