Run a SUMPRODUCT in VBA

**drchris** · 10-14-2010, 06:41 AM

H guys,
Can I get some help with the following:

As part of a Sub id like to run the following function and save it to a variable.

=SUMPRODUCT(($D$135:$D$181="Events and Exhibitio")*($E$134:$O$134="CFO"),$E$135:$O$181)

How can I do it?

I've tried this but I get a Type Mismatch error:

Dim vResult As Variant
vResult = _
[Sumproduct(($D$135:$D$181="Events and Exhibitio")*($E$134:$O$134="CFO"),$E$135:$O$181))]
MsgBox vResult

And also this:

Dim Rng1 As Range, Rng2 As Range, Rng3 As Range
Set Rng1 = Range("D135:D181")
Set Rng2 = Range("E134:O134")
Set Rng3 = Range("E135:O181")
MsgBox Application.SumProduct((Rng1 = "Events and Exhibitio") * (Rng2 = "CFO"), Rng3)

Again same Type Mismatch error!

Can I get some help please!

Thanks
Chris

**DonkeyOte** · 10-14-2010, 06:48 AM

In your first attempt you have a superfluous closing parentheses which is the cause of the error.

Generally speaking replicating worksheet SUMPRODUCTs from VBA are conducted via Evaluate.
(whether you opt to use [ ] rather than Evaluate( ) is something of a personal choice though the two can generate different results)

**drchris** · 10-14-2010, 06:51 AM

Thanks DonkeyOte,

How do I go about doing it via Evaluate then please?

**DonkeyOte** · 10-14-2010, 07:01 AM

Your first example is using Evaluate just in "shorthand" form [ ]

The equivalent longer version would be:

vResult = Evaluate("SUMPRODUCT($D$135:$D$181=""Events and Exhibitio"")*($E$134:$O$134=""CFO""),$E$135:O$181)")

note the double up of quotes... for this reason some prefer the [ ] method.

Evaluate will evaluate any unqualified ranges against the active sheet at run time unless otherwise stipulated.
It follows that if you want your SUMPRODUCT to refer to ranges on a sheet other than the active sheet at run time you should qualify the appropriate sheet either by

a) stipulating the relevant sheet in the SUMPRODUCT itself

SUMPRODUCT((Sheet1!A1:A100)*....)

or

b) qualifying the Evaluate call

With Sheet1
    vResult = .Evaluate("SUMPRODUCT(....)")
End With

As mentioned the [ ] and Evaluate( ) methods can generate different outputs (though not necessarily relevant here), example:

Dim strX As String, v1, v2
strX = "{1,2;3,4}"
v1 = [strX]
v2 = Evaluate(strX)

v1 generates a string whereas v2 generates a 2 dimensional variant array

**drchris** · 10-14-2010, 07:29 AM

thank you mate - makes absolute sense. its no wonder you are a guru!
ive just tried both [] and evaluate and it gives me the same results so ill stick to [] cause its a little bit cleaner. in any case though your explanation was spot on.
cheers
chris