# How many hour as a time range are there in given time intervals?

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?

PS: My native language is not English.

• have a look at `countifs` function.. – Aprillion Nov 1 '16 at 8:13
• A few things aren't clear. 1) are cols A and B independent of cols C and D (A&B = fault data, C&D = summary ranges)? 2) Are you counting events or hours (most rows show multi-hour faults; is that counted as 1 fault regardless of duration)? 3) The faults show overlapping times. Is each row considered a different fault regardless of the time frames of other faults? 4) Faults span multiple C:D time ranges. Does a fault get recounted during each C:D period in which it falls (e.g., 4 hr fault counted as 4 faults, 1 in each period)? (cont'd) – fixer1234 Feb 15 '18 at 1:22
• 5) Is a fault counted if any portion of the fault time falls within the C:D interval, regardless of how short? – fixer1234 Feb 15 '18 at 1:22
• @fixer1234, 1) Yes. 2) I am counting events. 3) Each row is considered a different fault. 4) Yes, a fault is get recounted if it falls in any C:D period. 5) Yes. – numand May 29 '18 at 6:37

``````=COUNTIFS(A:A,"<"&D2,B:B,">="&C2)
The only issue with your "Time Range" is that the start and end times overlap, meaning some values may be counted twice. To fix this, I chose to use `A<D` but `B>=C`. If this is not your expected behavior and you want them to be counted twice for the same "minute" then you can change `A<D` to `A<=D`.
Place the formula in `E2` then fill down.