SUMPRODUCT in VB

P

Paul Lautman

In a sheet with data as follows

A B
1 X 3
2 Y 4
3 X 5

I have the formula =SUMPRODUCT((A1:A3="X")*(B1:B3)*1) which returns 8.
So far so good.

But when I try to execute the following VB:

Debug.Print Application.WorksheetFunction.SumProduct((Range("A1:A3") =
"X"), Range("B1:B3"), 1)

I get Run-time error '13': Type mismatch.

So what did I do wrong?
 
B

Bob Phillips

Paul,

A couple of things.

Firstly, the *1 in the worksheet function is not required as there are
already 2 arrays being multiplied, so the Booleans are resolved to numbers
already.

Secondly, SUMPRODUCT won't work as a worksheetfunction, you need to evaluate
the formula

Evaluate("=SumProduct((A1:A3 =""X"")*(B1:B3))")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Paul,

SUMPRODUCT will work as a worksheet function if you use it in the
conventional way, such as

=SUMPRODUCT((A1:A3)*(B1:B3))

but when you are using SUMPRODUCT to create boolean arrays you have a
problem, as VBA can't create boolean arrays from other arrays. When the
arrays are worksheet ranges, you can then use Evaluate.

To answer your question, you can't AFAIK.

I would think that Evaluate is some order of magnitude less efficient than
WorksheetFunction, so only use it where required.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top