Sumproduct Date criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Try something like:

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

assuming col B contains dates
 
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
 
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
 
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.
 
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
 
Back
Top