How to filter out long term events that fall between two dates in Excel 2010?

Question

I'm trying to filter out events that may fall between a certain time period. These events are more than one day, so they have start dates and end dates.

I think the closest example to what I'm looking for is from this link: https://www.extendoffice.com/documents/excel/4647-excel-extract-records-between-two-dates.html. The only difference is that the data in Sheet 1 also have a start and end date that are greater than a day in difference, rather than existing for a singular day.

=IF(ROWS(A$5:A5)>$C$2,"",INDEX(Sheet1!A$2:A$22,SMALL(IF((Sheet1!$A$2:$A$22>=$A$2)*(Sheet1!$A$2:$A$22<=$B$2),ROW(Sheet1!A$2:A$22)-ROW(Sheet1!$A$2)+1),ROWS(A$5:A5))))

The issue is that currently the start dates may not coincide with the bounds of the study dates, but that the end dates of the data in Sheet 1 do fall within these bounds.

An example of what I'm hoping to do is this:

Example:

However, instead of having the date just be a single day, I'm hoping for a way to sort through data that have a start and end date.

Answer 1

This solution seems to work:
Assuming in Sheet1, columnA and columnB contain Start and End dates respectively.

Change the formula in C1 of Sheet2 to this, and follow the other instructions as given in your example.

=SUMPRODUCT((Sheet1!$A$2:$A$22>=A2)*(Sheet1!$A$2:$A$22<=B2)*(Sheet1!$B$2:$B$22>=A2)*(Sheet1!$B$2:$B$22<=B2))

Note: If this is not the output you expect then please share your expected input and output instead of from the sample.

Edit:
Based on your sample image I have updated the formulas. Hope this helps.

Formula for cell H2:

=SUMPRODUCT((($B$2:$B$16>=F2)*($B$2:$B$16<=G2)*($C$2:$C$16>=F2)*($C$2:$C$16<=G2))+((B2:B16<F2)*(C2:C16>=F2)*(C2:C16<=G2))+((B2:B16>=F2)*(B2:B16<=G2)*(C2:C16>G2))+((B2:B16<F2)*(C2:C16)>G2))

Formula for cell F5:

=IF(ROWS(F$5:F5)>$H$2,"",INDEX(B$2:B$16,SMALL(IF((($B$2:$B$16>=$F$2)*($B$2:$B$16<=$G$2)*($C$2:$C$16>=$F$2)*($C$2:$C$16<=$G$2))+(($B$2:$B$16<$F$2)*($C$2:$C$16>=$F$2)*($C$2:$C$16<=$G$2))+(($B$2:$B$16>=$F$2)*($B$2:$B$16<=$G$2)*($C$2:$C$16>$G$2))+(($B$2:$B$16<$F$2)*($C$2:$C$16)>$G$2),ROW(B$2:B$16)-ROW($B$2)+1),ROWS(F$5:F5))))

Don't forget to press Shift + Ctrl + Enter when using formula in F5. And then extend to other cells.

Output: