You cannot use the variables within the string being evaluated, you have to
use their values

Evaluate("SUMPRODUCT((" & EvalRange.Address & "=" & minAge & "),(" &
EvalRange.Address & "<" & MaxAge & ")," & sumrange.Address & ")")


--
__________________________________
HTH

Bob

"caroline" wrote in message
...
hello,
Using the search, I have tried to find a way to program SUMPRODUCT in VBA,
but I am not sure I am using it properly.
Can you check what I am doing wrong.
I do not want to copy the formula in Excel, i am after the final value.
Thanks
Caroline

For i = 1 To 200

Dim EvalRange As Range
Set EvalRange = Range(Cell1.Offset((i - 1) * 23, 2),
Cell1.Offset(i * 23 - 1, 2))
Dim sumrange As Range
Set sumrange = Range(Cell1.Offset((i - 1) * 23, X),
Cell1.Offset(i * 23 - 1, X))

For j = 1 To 3
minAge = Range("Ageband")(j, 0).Value
maxAge = Range("Ageband")(j, 1).Value

Cell2.Offset((i - 1) * j, 3).Value =
Evaluate("SUMPRODUCT((EvalRange=minAge),(EvalRang e<MaxAge),(sumrange))")

Next

Next

PS; cell1 and cell2 are defined as range etc