To avoid that confusion you can try out the below..

(Should have bee in one line. Just to show the individual pieces)

"=SUMPRODUCT(--(Sheet1!C3:C100=" &
Chr(34) & B & Chr(34) &
")*(Sheet1!E3:E100))"

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Dave, it looks strange but VBA interprets a double-double quote in a string
as a single double quote or CHR(34)

If this post helps click Yes
---------------
Jacob Skaria


"Dave" wrote:

Hi Sam,
B is text, and your code works. Thanks a lot!
Can you tell me why we need 3 lots of double quotes?
Thanks to all the others that responded.
Regards - Dave.

"Sam Wilson" wrote:

Me again, if B is text rather than a number, you'll need:

Evaluate("=SUMPRODUCT(--(Sheet1!C3:C100 = """ & B & """) * (Sheet1!E3:E100))")

"Dave" wrote:

Hi,
XL2003
I have the following Code:

Do Until Sheets("Sheet2").Cells(A, 4) = ""

B = Sheets("Sheet2").Cells(A, 4)

Sheets("Sheet2").Cells(A, 6) =
Evaluate("=SUMPRODUCT(--(Sheet1!C3:C100 = B) * (Sheet1!E3:E100))")

A = A + 1

Loop

In the Evaluate("Sumproduct... line, B is a variable, but it's not being read.
Can I use a variable in the above line?
Thanks in advance.
Dave.