Sumproduct Date criteria

[David]

I've searched Excel 2002 Help and given up on this one
How do I write a date criteria within SUMPRODUCT
I've tried =Sumproduct((rngA=1)*(rngB>01/01/2004))
but no joy
(.... Could this have been found in Help?)
tia

 

[Max]

Try something like:

=SUMPRODUCT((A1:A100=1)*(B1:B100>DATEVALUE("1-Jan-2004")))

assuming col B contains dates

 

[Frank Kabel]

Hi
try
=Sumproduct((rngA=1)*(rngB>DATE(2004,1,1)))

 

[Aladin Akyurek]

Either the date condition in a cell of its own, say, X2, and substitute X2
for the date value in the formula or use:

"01/01/2004"+0

instead of:

01/01/2004

 

[David]

Max, Frank, Aladin,
Thankyou all for your solutions - they all worked great
I was intrigued by Aladin's solution: "01/01/04"+0
How does that criteria work?
tia

 

[Aladin Akyurek]

A date value put between double quotes is a number in text format. Adding 0
coerces a text-formatted number into a real number which allows Excel read
it as such.

 

[Frank Kabel]

Hi Aladin
to be sure that this works also for different regional date settings I
would use a string in the format
YYYY/MM/DD
or
YYYY-MM-DD

so I would use
"2004/01/01"+0
or
"2004-01-01"+0