Jimbo,

The correct way to do that is:

MsgBox Evaluate("SUMPRODUCT(--(A2:A100=--" & startdate &
"),--(A2:A100<=--""2005-06-30""),
--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)")

HTH,
Bernie
MS Excel MVP


"Jimbo" wrote in message
...
Many thanks for your help in previous thread,

Now I would like to replace some of the criteria with variables but I am
stumped with the dates ... e.g. in the code below I would like to replace
"2005-06-01" with StartDate a variable which holds the value of the first
day of the current month

StartDate = Format(DateAdd("d", -Day(Now() - 1), Now()), "yyyy-mm-dd")

However many permutations of Cdate, Cstr, Format etc I cannot get it to
work
correctly would you mind showing me how it can be done

........... original code ............


evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--""2005-06-3
0""),
--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)")


Thanks again