Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProduct - Why won't it work?
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.Co unt (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:D" & myRowsO + 5 & "))") (not ok) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProduct - Why won't it work?
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:D" & 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 "Helen" wrote in message ... 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.Co unt (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:D" & myRowsO + 5 & "))") (not ok) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Will sumproduct work? | Excel Worksheet Functions | |||
SUMPRODUCT won't work on a row | Excel Worksheet Functions | |||
sumproduct doesn't work | Excel Worksheet Functions | |||
Will SUMPRODUCT work for this? | Excel Discussion (Misc queries) | |||
Sumproduct wont work | Excel Programming |