Check it in the immediate window:

set qtyRng = Range("D1:D200")
set StoreRng = Range("A1:A200")
set ItemRng = Range("C1:C200")
item = "A"
store = "ABCD"
? "sumProduct(--(" & StoreRng.Address & "=" & _
Store & "), --(" & ItemRng.Address & "=" & Item & "), " & _
QtyRng.Address & ")"

' Produces
sumProduct(--($A$1:$A$200=ABCD), --($C$1:$C$200=A), $D$1:$D$200)

so we see we are missing quotes around ABCD and A. So we add them:

"sumProduct(--(" & StoreRng.Address & "=""" & _
Store & """), --(" & ItemRng.Address & "=""" & Item & """),
" & _
QtyRng.Address & ")"

Now we test it again:

? "sumProduct(--(" & StoreRng.Address & "=""" & _
Store & """), --(" & ItemRng.Address & "=""" & Item & """),
" & _
QtyRng.Address & ")"

' Produces
sumProduct(--($A$1:$A$200="ABCD"), --($C$1:$C$200="A"), $D$1:$D$200)

so, looks good

CurrQty = Evaluate("sumProduct(--(" & StoreRng.Address & "=""" & _
Store & """), --(" & ItemRng.Address & "=""" & Item & """),
" & _
QtyRng.Address & ")")

--
Regards,
Tom Ogilvy






"Marcotte A" wrote in message
...
Thanks Tom. Still running into a problem though. I modified my code to
the
following...

CurrQty = Evaluate("sumProduct(--(" & StoreRng.Address & "=" & _
Store & "), --(" & ItemRng.Address & "=" & Item & "), " &
_
QtyRng.Address & ")")


The variables "Store" and "Item" are defined as Strings. StoreRng and
ItemRng each contain Strings (and empty cells). QtyRng contains integers
(and empty cells). When I Dim CurrQty as a Variant, the code executes,
but
CurrQty returns #VALUE. When I step through the macro line by line and it
gets to the CurrQty line, if I hover over "CurrQty" in the editor it says
"CurrQty = Error 2015".

When I dim CurrQty as a single, I get a Type Mismatch Error on that line.
Would empty cells in the ranges cause this? When I hover over the
*rng.Address parts, I get the correct range address (which all are the
same
dimensions), so I don't think that is causing the #VALUE error.

"Tom Ogilvy" wrote:

No. Sumproduct will not support this format in VBA. You have to use
the
Evaluate function which acts as a virtual worksheet cell.

myVar = Evaluate("Sumproduct(--(" & myRange.Address & "=" & OtherVar &
"),"
& AnotherRange.Address & ")"

--
Regards,
Tom Ogilvy


"Marcotte A" wrote in message
...
I want to use the sumproduct function to calculate a value in my
macro. I
need to use the "--" format of the function. Is this syntax correct?

myVar = application.worksheetfunction.sumproduct(--(myRange=OtherVar),
AnotherRange)