SumProduct - Why won't it work?

[Guest]

Hi,

I'm trying to use SumProduct in conjuction with some variables, and I can't
get the syntax correct. I've copied a few examples from the discussion
group but the result is always 0 (where x = 1, the SumProduct result should
be 200). There's a basic example below.

Company Order Description Price

2 4450 Apple £100.00
4 88 Banana £25.00
2 4451 Banana £25.00
2 4450 Apple £100.00
2 4552 Cherry £30.00

I want to find out the total for a particular order.

myRowsO = Sheets("Orders").Range("A6").CurrentRegion.Rows.Count (ok)

For x = 1 To myRowsO + 5 (ok)

OrderO = Sheets("Orders").Range("B" & x + 5) (ok)

TotalO = ActiveSheet.Evaluate("SUMPRODUCT((B6:B" & myRowsO + 5 & "=""" &
OrderO & """)*(D6" & myRowsO + 5 & "))") (not ok)

 

[Bernie Deitrick]

Helen,

You are putting a number into quotes, which converts it to a string. Try

TotalO = ActiveSheet.Evaluate("SUMPRODUCT((B6:B" & myRowsO + 5 & "=" &
OrderO & ")*(D6" & myRowsO + 5 & "))")

Also, you are adding 5 one more time than you need:

For x = 1 To myRowsO + 5

should be

For x = 1 To myRowsO

or even

For x = 1 To myRowsO - 1


HTH,
Bernie
MS Excel MVP