Sumproduct and dates

[Lee]

=SUMPRODUCT(--(SPRING4!N2:N1549<="1/31/07"),(SPRING4!M2:M1549))
Above is my formula that I can't get to work with dates. I want to sum the
M2:M1549 based on the date column of N. What am I doing that is wrong? The
date column is formated as mm/dd/yy.
Thanks to all for any help.

 

[Peo Sjoblom]

There is no need for the extra parenthesis

=SUMPRODUCT(--(SPRING4!N2:N1549<=--"1/31/07"),SPRING4!M2:M1549)

however you were looking for the text string 1/31/07, the unary minuses will
take care of that although it might be better to use either

=SUMPRODUCT(--(SPRING4!N2:N1549<=DATE(2007,1,31)),SPRING4!M2:M1549)

or


=SUMPRODUCT(--(SPRING4!N2:N1549<=H2),SPRING4!M2:M1549)

where you would put the date in a cell (H2 in my example)

 

[Jerry W. Lewis]

Excel dates are not text, they are the number of days since 1900. Try
DATEVALUE("1/31/07")
instead of "1/31/07"

Jerry

 

[Bob Phillips]

Never use an ambiguous date format like that. It will fail if used elsewhere
in the world. Whether you use DateValue or coerce with --.

Use an unambiguous date forma, 2007-01-31, or use Date(2007,1,31).



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Lee]

Thanks to all for getting back so quickly and it works!
Lee

 

[daddylonglegs]

Don't really need SUMPRODUCT here, SUMIF will suffice

=SUMIF(SPRING4!N2:N1549,"<="&DATE(2007,1,31),SPRING4!M2:M1549)

Thanks to all for getting back so quickly and it works!
Lee