SumIf (date range in Criteria)

[Irmgard Wesselb]

Is it possible to have a DATE RANGE in the criteria part of a SumIf function?

My formula =SUMIF(H2:H576,"<31/12/2009",I2:I576) correctly sums up all payments in column I where the payment date in column H is before 31 Dec. 2009.
However, it would be very useful to be able to sum up payments within a certain date range, e.g. between 1.1.2009 and 30.6.2009.

Any advice would be much appreciated.

Irma Wesselbaum

 

[ShredDude]

If using XL2007 or greater, read up on SUMIFS.

=SUMIFS(I2:I576,H2:H576,">1/1/2009",H2:H576,"<6/30/2009")

if prior to XL2007, you can achieve this result with SUMPRODUCT

like this:

=SUMPRODUCT(--(H2:H576)>1/1/2009,--(H2:H576)<30/6/2009,I2:I576)

great tutorial on SUMPRODUCT here: http://www.vbaexpress.com/forum/forumdisplay.php?f=98

 

[NBVC]

I am not sure that the SUMPRODUCT as you wrote will work.

I think it should be more like:

=SUMPRODUCT(--(H2:H572>"1/1/2009"+0),--(H2:H572<"30/6/2009"+0),I2:I572)

and to ensure the formula works in both Europe and North America, I like to employ the DATE function instead of hard coded date.

=SUMPRODUCT(--(H2:H572>DATE(2009,1,1)),--(H2:H572<DATE(2009,6,30)),I2:I572)

and you can use the more efficient SUMIF still...

=SUMIF(H2:H572,">"&DATE(2009,1,1),I2:I572)-SUMIF(H2:H572,">="&DATE(2009,6,30),I2:I572)

Note: that none of these formulas actually include the start and end dates... you will need to add/remove = signs.

for example to include the start/end dates with Sumproduct:

=SUMPRODUCT(--(H2:H572>=DATE(2009,1,1)),--(H2:H572<=DATE(2009,6,30)),I2:I572)

and with SUMIF

=SUMIF(H2:H572,">="&DATE(2009,1,1),I2:I572)-SUMIF(H2:H572,">"&DATE(2009,6,30),I2:I572)

 

[ShredDude]

nvbc:

Good points. I was winging it and it was late

Thanks for clarifying.

Shred

 

[Irmgard Wesselb]

Hallo NBVC,

Thank you very much for your prompt and detailed reply.
The last formula:
=SUMIF(H2:H572,">="&DATE(2009,1,1),I2:I572)-SUMIF(H2:H572,">"&DATE(2009,6,30),I2:I572)
works perfectly! With this formula I can now extract sums for any date range.

Regards,
Irma Wesselbaum

 

[NBVC]

You are welcome.

I probably should have also mentioned that you can reference cells containing the start and end dates, so that you don't have to manually change the formula all the times.

So instead of:

=SUMIF(H2:H572,">="&DATE(2009,1,1),I2:I572)-SUMIF(H2:H572,">"&DATE(2009,6,30),I2:I572)

you can enter start and end dates in, say, X2 and Y2 and use formula:

=SUMIF(H2:H572,">="&X2,I2:I572)-SUMIF(H2:H572,">"&Y2,I2:I572)

Now you can just replace the dates in X2 and Y2..

and if you were to copy the formula down to get various sums for various date ranges that may be in columns X and Y, then you would need to make the SUMIF ranges absolute, by freezing the column/row references.

=SUMIF($H$2:$H$572,">="&X2,$I$2:$I$572)-SUMIF($H$2:$H$572,">"&Y2,$I$2:$I$572)

copied down.

Note: please double-check the ranges.. my ranges were not exactly the same as the ranges you originally identified.

 

[Irmgard Wesselb]

to NBVC:

Thanks again! Referencing cells for the start and end dates is ideal.

Regards,
Irma Wesselbaum