Sumproduct and dates

  • Thread starter Thread starter Lee
  • Start date Start date
L

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.
 
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)
 
Excel dates are not text, they are the number of days since 1900. Try
DATEVALUE("1/31/07")
instead of "1/31/07"

Jerry
 
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)
 
Don't really need SUMPRODUCT here, SUMIF will suffice

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

Lee said:
Thanks to all for getting back so quickly and it works!
Lee
Click to expand...
 
Back
Top