SUMPRODUCT - NO SUMPRODUCT!

[Guest]

I am using Excel 97 & Excel XP.

I have a formula
=SUMPRODUCT((EA!$F$2:$F$20000="Apples")*(EA!$B$2:$B$20000>=28/11/2004)*(EA!$B$2:$B$20000<=4/12/2004))

but it is not giving me a result. I have also tried:

=SUMPRODUCT(-(EA!$F$2:$F$20000="Apples"),-(EA!$B$2:$B$20000>=28/11/2004),--(EA!$B$2:$B$20000<=4/12/2004))

but that also fails, can someone tell me where I am going wrong, please as
with either of the two below, using only two criteria it works fine!

=SUMPRODUCT(--(EA!$F$2:$F$20000="Apples"),--(EA!$B$2:$B$20000>=28/11/2004))

=SUMPRODUCT((EA!$F$2:$F$20000="Apples")*(EA!$B$2:$B$20000>=28/11/2004))

Mark

 

[Don Guillett]

use this idea or refer to a cell with the appropriate date
=SUMPRODUCT((ChecksA>DATEVALUE("1/6/2004"))*(ChecksA<DATEVALUE("4/12/2004"))*ChecksD)

 

[Bob Phillips]

You have to properly construct the date, such as

=SUMPRODUCT(--(EA!$F$2:$F$20000="Apples"),--(EA!$B$2:$B$20000>=--"2004-11-28
"),--(EA!$B$2:$B$20000<=--"2004-12-04"))

or

=SUMPRODUCT(--(EA!$F$2:$F$20000="Apples"),--(EA!$B$2:$B$20000>=DATE(2004,11,
28)),--(EA!$B$2:$B$20000<=DATE(2004,12,4)))



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ron Coderre]

In your example, the 28/11/2004 does NOT refer to a date...it is
actually dividing 28 by 24 and dividing that result by 2004.
Consequently, the value equates to 0.00127018689892941, and not the
date you are looking to use.

Try this:
=SUMPRODUCT((EA!$F$2:$F$20000="Apples")*(EA!$B$2:$
B$20000>=DATEVALUE("28/11/2004"))*(EA!$B$2:$B$20000<=DATEVALUE("4/12/2004")))

Also, are you sure about your date format? You used day/month/year,
which may be correct.

For my region, I have month/day/year. So I had to use:
=SUMPRODUCT((EA!$F$2:$F$20000="Apples")*(EA!$B$2:$B$20000>=DATEVALUE("11/28/2004"))*(EA!$B$2:$B$20000<=DATEVALUE("12/4/2004")))

Does that help?

Ron