Re: 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:M1549)

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" <lee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:eJ6TLohyHHA.3848@xxxxxxxxxxxxxxxxxxxxxxx
=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



.

Relevant Pages

  • Re: format line wrap?
    ... Take a long string and wrap it into a block of lines. ... I care as much about your client as you do care about my feeble ... Quis aute iure reprehenderit in ... voluptate velit esse cillum dolore eu ...
    (comp.lang.lisp)
  • Re: grep is too slow...
    ... JM> # on next line, we only care about $ortho, the orthographic string ... you are searching. ...
    (perl.beginners)
  • Re: Multiple Steps OLE DB generated errors.
    ... and D7 using ADO OLE Connection. ... First you do not need to convert the date parameters to string just assign ... Second if you do convert them make sure that you have the correct format ... After all of the above are taken care then try to collect the error ...
    (borland.public.delphi.database.ado)
  • Re: I added Date$ to a query field and am getting shaky results
    ... in a query field, ... Date$ doesn't "honor" the Windows date and time format settings for ... Date$ returns a string, Nowreturns a date. ... won't appear to care, while it will care if it has to handle a String ...
    (comp.databases.ms-access)
  • Re: A more structured approach
    ... For the purpose of a "string compare" routine that'll be ... you might care. ... some assembly language, you don't. ... But I would then pass that as a parameter to my "find environment ...
    (alt.lang.asm)