I would do it this way: You may need to add code to get qty * cost if there
is a column with qty in it.

Sub MyTotals()
Dim i As Integer
Dim total As Double


Myarray = Range("A2:B6")

For i = 1 To UBound(Myarray)
If Trim(Myarray(i, 1)) = "car" Or Trim(Myarray(i, 1)) = "radio" Then
total = total + Myarray(i, 2)
End If
Next i

MsgBox (total)

End Sub




"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