You cannot just run an Excel function in VBA, they are different beasts.

This should work for you

Sub test()
total =
Activesheet.Evaluate("SUMPRODUCT(((A2:A5=""radio"" )+(A2:A5=""car"")),B2:B5)"
End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Richard" wrote in message
...
I have a 'sumproduct' statement that works as a function when typed in an
individual cell - but I can't get a similar command to work inside of a
Macro

column A contains product type - e.g. radio, car, etc.
column B contains product cost - e.g. $100.00

I want the total cost of all the radios and cars in column B

This formula works in a cell:
=SUMPRODUCT( (A2:A5="radio")*(B2:B5)+(A2:A5="car")*(B2:B5) )

But I can't get similar formula to work in a macro:
Sub test()
total =SUMPRODUCT( (A2:A5="radio")*(B2:B5)+(A2:A5="car")*(B2:B5) )
End Sub

I've tried various things - like defining range variables; using
worksheetfunction.sumproduct; and using variables for "radio" and "car" --
but no luck.

How can I do this in an Excel Macro?
--
Richard