Calculating time conflicts

[Cambosity100]

Hi all !

     I am so close but no cigar. Please view attached file. I am trying to count shifts that conflict with a specified time frame. All data is imaginary but moving through all potential counting/not counting scenarios. I have highlighted errors. It is these last 2 scenarios that is eluding me... 

     With thanks in advance to the Excel community :)

[#2]

@Cambosity100 

The basic formula is

= COUNTIFS(
   ShiftStart, "<"&CriterionEnd,
   ShiftEnd, ">"&CriterionStart)

It is still possible to hit problems with rounding error so you could build in an allowance of 1sec overlap which would be ignored.

[#3]

@Peter Bartholomew 

As always thankyou Peter. I don't understand the 1 second thing. Would that resolve row 14 ? Also I must thank you for introducing me to defined ranges and references it makes nutting out formulae so much easier. Please find worksheet with your formula.

     Kindest regards

[#4]

@Cambosity100 

In the attached, I have reduced the criteria range by 1 ms at either end to resolve the inequality issue.

Since the solution uses COUNTIFS, it is also possible to perform the calculation for multipl criteria ranges simultaneously by using an array formula.