SUMIF date range

[jhampel106]

Can someone help me with a SUMIF formula? This seems easy enough, but I just can't get it into a concise formula.

Sheet A contains: Beginning Date in cell E2 and Ending Date in Cell F2.
Sheet B contains a list of dates in column L and an amount value in Column V. The entire sheet is sorted assending by date in Column L.

In Sheet A cell K2, I want the sum all the Sheet B, Column V cells only if the value in Column L falls within the date range of Sheet A Column E2 and F2.
Thank you for your help.

 

[johnbird1988]

Can someone help me with a SUMIF formula? This seems easy enough, but I just can't get it into a concise formula.

Sheet A contains: Beginning Date in cell E2 and Ending Date in Cell F2.
Sheet B contains a list of dates in column L and an amount value in Column V. The entire sheet is sorted assending by date in Column L.

In Sheet A cell K2, I want the sum all the Sheet B, Column V cells only if the value in Column L falls within the date range of Sheet A Column E2 and F2.
Thank you for your help.

Try this below
Sheet1 = Sheet A
Sheet2 = Sheet B
I am not sure how long your list is in column L on Sheet B but i have only gone to 5 in this example.

=SUMPRODUCT((Sheet2!$L$2:$L$5>=Sheet1!E2)*(Sheet2!$L$2:$L$5<=Sheet1!F2),Sheet2!$M$2:$M$5)

Cheers

John

 

[NBVC]

If using XL2007 or 2010

=Sumifs('Sheet B'!V:V,'Sheet B'!L:L,">="&E2,'Sheet B'!L:L,"<="&F2)

in other versions:

=Sumif('Sheet B'!L:L,">="&E2,'Sheet B'!V:V)-Sumif('Sheet B'!L:L,">"&F2,'Sheet B'!V:V)

 

[jhampel106]

By the end of the year, Sheet B will have over 88,000 lines in it, so I need to accommodate a large spread in the range.

 

[NBVC]

The SUMIFS and SUMIF combo are better suited for large ranges than SUMPRODUCT.

 

[johnbird1988]

=SUMPRODUCT((Sheet2!$L:$L>=Sheet1!E2)*(Sheet2!$L:$L<=Sheet1!F2),(Sheet2!$M:$M))

Chenged slightly to take into account the above!

Cheer, John

 

[NBVC]

Using Sumproduct with whole columns is not advisable... very inefficient for processing.

 

[jhampel106]

NBVC-
This formula works great. Thanks so much.