SUMPRODUCT - NO SUMPRODUCT!
 

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

SUMPRODUCT - NO SUMPRODUCT!
 

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

--
Don Guillett
SalesAid Software

"Mark" wrote in message
...
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

SUMPRODUCT - NO SUMPRODUCT!
 

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)


"Mark" wrote in message
...
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

SUMPRODUCT - NO SUMPRODUCT!
 

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=487624