Jimbo,

The code worked for me in both its forms. I will send you a working version
privately.

HTH,
Bernie
MS Excel MVP


"Jimbo" wrote in message
...
Thanks for your input Bernie ... I had tried that approach prior to
starting
this thread but it doesn't appear to work ...
... hence my fiddling about with Format, Cdate, Cstr etc since the
original
code (which works) contains a string representing the first of June 2005
albeit in yyyy-mm-dd format.
Where am I goijng wrong ?

Regards
Jimbo

I always seem to have trouble with dates ...



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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