Count number of rows where any date between start and finish are between 2 dates

In the complete absence of any expected results, a guess. In D2 copied across:

=SUMPRODUCT(($A$3:$A$39<=D1)*($B$3:$B$39>=D1))

Another interpretation:

Please try in D3 and copy right and down:

Formula:

Please Login or Register  to view this content.

What comment?

Have you tried either of the suggestions offered?

Could you please provide expected results (manually calculated) for D2 to D6?

Please try in D2 of workbook in post #3 and copy to right

Formula:

Please Login or Register  to view this content.

You know exactly what it all means - we don't, so closer explanations are needed.

For my benefit, could you please explain the calculation you did mentally to arrive at 3 for the first count?

I now understand the numbers you gave. I'll leave this to Hans, as I have no idea how to do it without the large matrix below.

Give him time. This is going to be complex, if indeed possible. Patience is the key! I shall watch with interest.

Please try in D2 and copy to right:

Formula:

Please Login or Register  to view this content.

I have been asked to provide some explanation about this formula.

If period 1 does not coincide with period 2, then both the start date and end date of period 1 are less than the start date of period 2 or the start date and end date of period 1 are greater than the end date of period 2.
In all other cases, the two periods are (partly) consided.

To determine this easily, the function MEDIAN is used.
The MEDIAN function finds the middle number from a series of numbers.
If there are an even number of numbers, the average of the middle two numbers.
So the median of 4 numbers is the average of the two middle numbers.

The formula calculates the median of the two starting days and the two ending days of the two periods.
If 2 periods do not coincide, the median will always lie between the end date of the earliest period and the start date of the latest period, so the median will not lie in either period.
On the other hand, if 2 periods do partially coincide, then the median of these four days cannot fall outside the two periods.
That is why it is tested whether the median falls within one of these periods.
And if so, that counts as 1.

With function MAP-LAMBDA this is then done for all periods in the column and
with function SUM all 1's are counted.

I found it difficult to explain this formula, but I hope the explanation helps.

If you have any further questions, don't hesitate to ask them here.

That's really helpful - thank you!

Or

Formula:

Please Login or Register  to view this content.

LOL!

Looks like some of us were over-complicating this big time!!!

@AliGW:

You had it (almost!) correct in your post (#2)

=SUMPRODUCT(($A$3:$A$39<=D1)*($B$3:$B$39>=D1))

vs

=SUMPRODUCT(($A$3:$A$39<=E1)*($B$3:$B$39>=D1))

LOL, yes a little bit overcomplicating , but nevertheless it was fun to work on the formula.

And it was useful learning, too.

@AliGW:

You had it (almost!) correct in your post (#2)

Doh!!!