SumProduct/Date Ranges

[Guest]

This statement returns the correct result for records meeting these
conditions and dated before 11/8/2004:
=SUMPRODUCT(--($G$2:$G$2495="no")*($A$2:$A$2495="Colin
james")*($E$2:$E$2495<DATE(2004,11,8)))

This statement returns 0 for the number of records meeting the conditions
and dated between 11/8 and 11/12 inclusive. I know there are 23 such records.

=SUMPRODUCT(--($G$2:$G$2495="no")*($A$2:$A$2495="Colin
james")*($E$2:$E$2495>=(2004/11/8))*($E$2:$E$2495<=(2004/11/12)))

What am I doing wrong?

thanks

 

[Guest]

You left out the DATE to identify your dates. I didn't notive anything else
at first glance ...
Try:

=SUMPRODUCT(--($G$2:$G$2495="no")*($A$2:$A$2495="Colin
james")*($E$2:$E$2495>=DATE(2004/11/8))*($E$2:$E$2495<=DATE(2004/11/12)))


tj

 

[Guest]

I neglected to changes the slashes to commas in the DATE funtion sections of
the new function. See below.

=SUMPRODUCT(--($G$2:$G$2495="no")*($A$2:$A$2495="Colin
james")*($E$2:$E$2495>=DATE(2004,11,8))*($E$2:$E$2495<=DATE(2004,11,12)))

tj

 

[Bob Phillips]

=SUMPRODUCT(--($G$2:$G$2495="no"),-($A$2:$A$2495="Colin
james"),--($E$2:$E$2495>=--("2004/11/08")),--($E$2:$E$2495<=--("2004/11/12")
))



--

HTH

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