Using SumProduct Worksheet Function in VBA

**jasoncw** · 02-05-2009, 04:46 PM

Hi Guys.

I'm having a hard time figuring out how to use the SumProduct worksheet function in VBA. The below worksheet formula works:

=SUMPRODUCT((accProv=D2)*(accFYE=E2)*(accAcc))

- or -

=SUMPRODUCT(--(accProv=D2),--(accFYE=E2),(accAcc))

I tried the below variations in VBA, and I keep getting run-time error 13. Any ideas?

Please Login or Register  to view this content.

The dynamic named ranges accProv, accFYE, and accAcc are on sheet Track Changes, and cells D2 and E2 are on sheet Production.

TIA

Jason

**DonkeyOte** · 02-05-2009, 05:12 PM

Sumproduct in VBA doesn't work quite the same way... normally better to use Evaluate, ie

Please Login or Register  to view this content.

(Evaluate can be called relative to a specific sheet or at Application level)

**jasoncw** · 02-05-2009, 05:22 PM

Thanks, Donkey. I just found this out doing a Google search as well.

Jason

**DonkeyOte** · 02-05-2009, 05:24 PM

Yeah I learnt the beauty of Evaluate in conjunction with Sumproduct when I was building my little Sumproduct Analyser add-in ...
(with help from others I should add!)