SUMPRODUCT in VB

[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?

 

[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)

 

[Paul Lautman]

Hi Bob, well according to
http://msdn.microsoft.com/library/d...ff2000/html/xlmscListOfWorksheetFunctions.asp
(or http://tinyurl.com/3f9a3 if that url gets broken), SUMPRODUCT can
be used as a WorksheetFunction.

And indeed it does, but not with the '= "A"' in there.

So I wanted to know how to write it so that it would work in
WorksheetFunction.

If evaluate can do all that WorksheetFunction can, then why have
WorksheetFunction?

 

[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)

 

[Dana DeLouis]

This is the same solution as Bob's. Just slightly different.

ans = WorksheetFunction.SumProduct([0+(A1:A3="x")], Range("B1:B3"))

 

[Bob Phillips]

Dana,

I think the OP has probably left but I am impressed.

Bob