I want to count hours as a time range in given time intervals. It is a bit difficult for me to put what I want to words in English so let me explain waht I want with an example:

```
A B C D E
1 Start of Fault End of Fault Time Ranges Number of Faults
2 12:00 13:00 00:00 01:00 X
3 12:00 14:00 01:00 02:00 X
4 08:30 23:00 02:00 03:00 X
5 00:00 24:00 03:00 04:00 X
. ... ... ... ... ...
```

As you can see, I have starting and ending time of a fault. On C and D coloums, I have time ranges starting from 00:00 and ending at 24:00. Also,

```
X = How many faults did happen between a time range (00:00-01:00)?
```

X must scan A and B columns and if a given time range (say 12:00-13:00) is included in any time intervals (say 12:00-13:00, 08:30-23:00), X is the number of intersections between a given time range and time intervals.

I tried to calculate X using this Excel folmula:

```
=IF(AND($A$2>=C2;$B$2<=D2);1;0)+IF(AND($A$3>=C2;$B$3<=D2);1;0)+IF(AND($A$4>=C2;$B$4<=D2);1;0)+IF(AND($A$5>=C2;$B$5<=D2);1;0)
```

But this formula works only if time range and time insterval are equal.

Is there a way for me to get value of X by using an Excel formula?

Thank you in advance.

PS: My native language is not English.

`countifs`

function.. – Aprillion Nov 1 '16 at 8:13