Formula to Count Occurence of Dates between two Other Dates Via Array Formula

Question

I use this example to illustrate my problem:

I can't get E2 to work. Any ideas?

What I try to do: I have data showing me a start date (column from) and an end date (column end). A2+B2 is one instance, A3+B3 is another. Cell C2 holds an event. I want to count in E2 all instances for which the event is between the start and the end date. I expect E2 to be 2, but it's 1. I tried to split up the formula by using column D, where I calculate the result for each instance separately. That seems to work, i.e. it shows 1 for all instances. But I can't seem to make the array formula in E2 get to work. Any ideas?

Answer 1

Perhaps SUMPRODUCT:

=SUMPRODUCT(--($C$2>$A$2:$A$3),--($C$2<$B$2:$B$3))

If you really need an array formula, perhaps:

=SUM(--($C$2>$A$2:$A$3)*--($C$2<$B$2:$B$3))