Sumproduct and dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lee - 19 Jul 2007 16:32 GMT

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

Signature

Lee

Reply to this Message

Peo Sjoblom - 19 Jul 2007 16:46 GMT

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)

Signature

Regards,

Peo Sjoblom

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

Reply to this Message

Jerry W. Lewis - 19 Jul 2007 16:52 GMT

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

Jerry

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

Reply to this Message

Bob Phillips - 19 Jul 2007 17:13 GMT

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).

Signature

HTH

Bob

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

> Excel dates are not text, they are the number of days since 1900. Try
> DATEVALUE("1/31/07")
[quoted text clipped - 9 lines]
>> date column is formated as mm/dd/yy.
>> Thanks to all for any help.

Reply to this Message

Lee - 19 Jul 2007 18:28 GMT

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

Reply to this Message

daddylonglegs - 22 Jul 2007 13:54 GMT

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

Reply to this Message