sumif < date

[David]

Hi Group,

Having a little trouble doing a SumIf, have a list of dates and a column I
am trying to sum if the date is less than a certain date. The dates go back
to 1/1/1999 and I want to sum value that are less than 12/31/1999, as an
example.

Thanks,
David

 

[daddylonglegs]

Hello David,

You can do that like this

=SUMIF(A:A,"<"&DATE(1999,12,31),B:B)

where dates are in column A and amounts to sum in column B

or you can put your date in C1 and use

=SUMIF(A:A,"<"&C1,B:B)

 

[Dave Peterson]

=sumif(a1:a10,"<"&date(1999,12,31),b1:b10)

you sure you didn't mean less than or equal to 12/31/1999????

 

[T. Valko]

Try this:

=SUMIF(A1:A100,"<"&DATE(1999,12,31),B1:B100)

Or, use a cell to hold the date criteria:

D1 = 12/31/1999

=SUMIF(A1:A100,"<"&D1,B1:B100)

 

[David]

Thank you. This works great. I really did have a hard time, I knew that the
"" had to go in there somewhere, but just had the hardest time.

David

 

[Rick Rothstein \(MVP - VB\)]

Here is another way to write it...

=SUMIF(A1:A100,"<=1999-12-31",B1:B100)

where the order of the date parts must be as shown (and I changed your < to
<= as I'm pretty sure you want to include each day in 1999 in your
summation). Of course, you could also write it this way as well...

=SUMIF(A1:A100,"<2000-1-1",B1:B100)

saving yourself from having to type 3 extra characters.<g>

Rick