Problem with getting a count of event start and end date overlaps

[darksidekilo6]

First time poster but not for a lack of post surfing. I have a list of events with start and end dates for specific units. I am trying to determine if any of the events for a specific unit have start and end dates that overlap or fall between each other and then count the number that meet the below criteria:

X = event start date
y = event end date
a = all start dates
b = all end dates

1. x is between a and b, but y is >= b
2. x is <= a and y is > a but <= b
3. Both x and y are between a and b
4. x is >= to a, but y is >= b

I am using the following formula in column D to return "Overlap" if overlaps that meet the above criteria exist or return "Ok" if no overlaps meeting the above criteria exist.

Column A is the unit uniquew designator
Column B is all of the start dates
Column C is all of the end dates

=IF(SUMPRODUCT(--($A2=$A$2:$A$12),--($C2>=$B$2:$B$12),--($B2<=$C$2:$C$12))=1,"OK","Overlap")

I have used numerous formulas to determine if overlaps exist all of which seemed to have worked. The above is just the most recent formula that appears to work correctly returning the appropriate text.

Now in column E, I am trying to count the number of overlaps for a specific unit if Column D returns overlap. Below is the formula I am currently using that returns random results.

=IF($D2="Overlap",SUMPRODUCT(--($A2=$A$2:$A$12),--($C2>=$B$2:$B$12),--($B2<=$C$2:$C$12)),0)

Here are the results:

[TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD]Col A
[/TD]
[TD]Col B
[/TD]
[TD]Col C
[/TD]
[TD]Col D
[/TD]
[TD]Col E
[/TD]
[TD]Col F
[/TD]
[/TR]
[TR]
[TD]Unit
[/TD]
[TD]Start Date
[/TD]
[TD]End Date
[/TD]
[TD]Test
[/TD]
[TD]Count
[/TD]
[TD]Remarks
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]5/22/2016
[/TD]
[TD]5/30/2016
[/TD]
[TD]Overlap
[/TD]
[TD]4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]5/23/2016
[/TD]
[TD]5/25/2016
[/TD]
[TD]No
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]5/23/2016
[/TD]
[TD]5/25/2016
[/TD]
[TD]Overlap
[/TD]
[TD]3
[/TD]
[TD]Col E should be 4
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]5/25/2016
[/TD]
[TD]5/28/2016
[/TD]
[TD]Overlap
[/TD]
[TD]4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]5/27/2016
[/TD]
[TD]5/30/2016
[/TD]
[TD]No
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]5/28/2016
[/TD]
[TD]5/30/2016
[/TD]
[TD]Overlap
[/TD]
[TD]3
[/TD]
[TD]Col E should be 4
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]7/5/2016
[/TD]
[TD]7/15/2016
[/TD]
[TD]No
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Thanks in advance for any help on getting me back on track.

Sean

 

[darksidekilo6]

Bump! Still looking for possible methods. Conducted some more research but I still can't get a consistent number of overlapping events for a unit. I've tried SUMPRODUCT, IF(OR, and IF(AND with no luck.