sumproduct in Excel Macro

[Guest]

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?

 

[Bob Phillips]

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)

 

[Dave]

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

 

[Guest]

Try using evaluate...

total =Evaluate(SUMPRODUCT(
(A2:A5=""radio"")*(B2:B5)+(A2:A5=""car"")*(B2:B5) )")

 

[Guest]

Bob,
Except for having to add a closing parentheses, your statement worked great.

However, it does look like this part of the statement is treated as text:
"SUMPRODUCT(((A2:A5=""radio"")+(A2:A5=""car"")),B2:B5)"

How would I handle the more general case where the range (i.e. A2:A5), were
really a variable, say myRng1?
And what would I do if my criteria (i.e. radio) were also a variable, say
myCriteria1?

 

[Bob Phillips]

total = ActiveSheet.Evaluate("SUMPRODUCT(((" & myRng1.Address & _
"=""" & myVar1 & """)+(" & myRng1.Address & _
"=""" & myVar2 & """)),B2:B5)")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)