Sumproduct Will Not Find Date
 

Does the SUMPRODUCT function find dates? An example of my formula,as
follows, will not work i.e.returns "0":

=SUMPRODUCT(($A$1:$A$200="26/10/2004)*($B1$B200=$C1))

Please let me know if when using date references I have to do something
extra?

Thanks

Andy


--
andyp161
------------------------------------------------------------------------
andyp161's Profile: http://www.excelforum.com/member.php...o&userid=11654
View this thread: http://www.excelforum.com/showthread...hreadid=506132

Sumproduct Will Not Find Date
 

Hi Andy

You are missing a double quote at the end of your date. You also need to
coerce this text to be a date value, by preceding with the double unary
minus so,
=SUMPRODUCT(($A$1:$A$200=--"26/10/2004")*($B1$B200=$C1))
Personally, I always use the DATE() function
=SUMPRODUCT(($A$1:$A$200=DATE(2004,10,26))*($B1$B2 00=$C1))

--
Regards

Roger Govier


"andyp161" wrote
in message ...

Does the SUMPRODUCT function find dates? An example of my formula,as
follows, will not work i.e.returns "0":

=SUMPRODUCT(($A$1:$A$200="26/10/2004)*($B1$B200=$C1))

Please let me know if when using date references I have to do
something
extra?

Thanks

Andy


--
andyp161
------------------------------------------------------------------------
andyp161's Profile:
http://www.excelforum.com/member.php...o&userid=11654
View this thread:
http://www.excelforum.com/showthread...hreadid=506132

Sumproduct Will Not Find Date
 

=SUMPRODUCT(($A$1:$A$200=--"2004-10-26")*($B1:$B200=$C1))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"andyp161" wrote in
message ...

Does the SUMPRODUCT function find dates? An example of my formula,as
follows, will not work i.e.returns "0":

=SUMPRODUCT(($A$1:$A$200="26/10/2004)*($B1$B200=$C1))

Please let me know if when using date references I have to do something
extra?

Thanks

Andy


--
andyp161
------------------------------------------------------------------------
andyp161's Profile:
http://www.excelforum.com/member.php...o&userid=11654
View this thread: http://www.excelforum.com/showthread...hreadid=506132