Sumproduct and dates
 

=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.
--
Lee

Sumproduct and dates
 

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:M15 49)

or


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

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


--
Regards,

Peo Sjoblom



"Lee" wrote in message
...
=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.
--
Lee

Sumproduct and dates
 

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

Jerry

"Lee" wrote:

=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.
--
Lee

Sumproduct and dates
 

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)

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

Jerry

"Lee" wrote:

=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.
--
Lee

Sumproduct and dates
 

Thanks to all for getting back so quickly and it works!
Lee
"Lee" wrote in message
...
=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.
--
Lee

Sumproduct and dates
 

Don't really need SUMPRODUCT here, SUMIF will suffice

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

"Lee" wrote:

Thanks to all for getting back so quickly and it works!
Lee
"Lee" wrote in message
...
=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.
--
Lee