Sumproduct Will Not Find Date

A

andyp161

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
 
R

Roger Govier

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$B200=$C1))
 
B

Bob Phillips

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

--

HTH

Bob Phillips

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