SUMIFS using date ranges

[jrtaylor]

I've attached a workbook with two tabs. One tab ("Serial Dates") has a single row of dates listed in order. (although some dates are missing).

The second tab has two columns of dates for each row: An Open date and a Close date. Next to the Open and Close date is a value (Column S).

In the first tab, for every row, I want to get a sum of all values (in column S of the second tab) which would be included in the date ranges shown in the second tab.

I've tried using SUMIFS without success. I've also shown some manual calcs to illustrate what I'm looking for. Please let me know if I need to clarify anything. As always I appreciate the help of you Excel geniuses!

[Olly]

Here's the formula I think you need in Serial Dates!C3, and copied down:

Please Login or Register  to view this content.

However - can you please clarify the value you expect in B7?

I expected you to include S11:S16, as these rows overlap 30 July - but you've stated this value should include S3:S10, all of which are CLOSED on 27 July?

If my assumption on what your logic SHOULD be is incorrect, please clarify.

[Bo_Ry]

Please try at B4
=SUMIFS('Date Ranges'!S:S,'Date Ranges'!A:A,"<"&A4+1,'Date Ranges'!B:B,">="&A4)

B7 should sum(S11:S16)

[jrtaylor]

Thanks. Yes you're both correct re: B7..