Having Issues With Conditional Partial Vertical Column Sums Based On Date Ranges

[Aimee S.]

https://imgur.com/a/G6LurIo

Hello Folks.

Hoping someone can assist with this dilemma. The image linked herein shows what the spreadsheet looks like. How would I write formulas in cells I11, J11, K11, and L11 such that they all reference the entered start and end dates in cells J5 and K5 and subsequently SUM the corresponding columns D, E, F and G using the dates in Columns B and C as a guide?

For example, if the Review Period is 12/23/18 to 1/12/19, in cell I11 I would want the formula to SUM cells D4 through D7. If the Review Period is 12/30/18 through 1/12/19, I would only want the formula in cell I11 to SUM values in D5 through D6 (see how the Start and End Dates match up).

Any help will be immensely appreciated!!!!
Aimee in MadTown WI

 

[Fluff]

How about in I11 copied across
=SUMPRODUCT(($B$4:$B$23>=$J$4)*($C$4:$C$23<=$K$4),D4:D23)

 

[Aimee S.]

That works wonderfully, Fluff. Good Lord how did I not consider SUMPRODUCT - my mind was racing with INDEX and FIND and MATCH and -2 columns or +2 columns etc here or there. Occam's Razor and all that I suppose! Thanks so much!!!!!!

 

[Fluff]

You're welcome & thanks for the feedback