Count total occurrence of time in a series of time ranges: Urgent Help Please?

[Afomaci]

[FONT=&quot]Hi, please i have three columns
Data is arranged as follows[/FONT]
[TABLE="class: grid, width: 500, align: center"]
[TR]
[TD]Arrival Time[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]13:23 [/TD]
[TD]14:24 [/TD]
[TD]19:50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13:23 [/TD]
[TD]14:24[/TD]
[TD]19:50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13:24 [/TD]
[TD]13:05[/TD]
[TD]17:20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13:25 [/TD]
[TD]12:12[/TD]
[TD]22:22[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] :
:
:
:
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13:22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]
[FONT=&quot]


[/FONT]
[FONT=&quot]Column A has 1440 arrival times to represent 1440 minutes in twenty four hours (occupies A1:A1140), column B and C have 100 intervals/range Start - Finish (Ocupies B1:B100 and C1:C100)[/FONT]
[FONT=&quot]I'm trying to count the total occurrence of each arrival time occurs between the entire range (Start Time- Finish Time) i.e For 13:23, the total number of occurrences of 13:23 in the 100 start time - finish time ranges represented in column B and C.[/FONT]
[FONT=&quot]I used this formula: for A1
=SUMPRODUCT((COUNTIFS(A1,">="&$B$1:$B$100)), COUNTIFS(A1, "<=" &$B$1:$B$100))[/FONT]
[FONT=&quot]the answers i get is increasing from 38, 38, 38, 39, 39, 40, 40, 41, 42, 43, 40, 40, 38, 39, 37, 37, 36, 36, 36, 35, 35, 34, 33,.................., 2, 2, 1,1,1,1, 0,0,0,0,0,0,0,0
It gets to zero and the rest of it are zeros.
on doing a manual count, this is incorrect.[/FONT]
[FONT=&quot]How do i rectify my formulae?
Thanks

Attached is screenshot of a section of my excel sheet, the result is in the column called "count"

[/FONT]
[TABLE="width: 563"]
[TR]
[TD]Number of Minutes[/TD]
[TD]Arrival Time[/TD]
[TD]COUNT[/TD]
[TD]START [/TD]
[TD]FINISH[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]13:23[/TD]
[TD="align: right"]38.00[/TD]
[TD="align: right"]13:23[/TD]
[TD="align: right"]19:37[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]13:24[/TD]
[TD="align: right"]38.00[/TD]
[TD="align: right"]13:33[/TD]
[TD="align: right"]20:22[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]13:25[/TD]
[TD="align: right"]38.00[/TD]
[TD="align: right"]13:39[/TD]
[TD="align: right"]21:24[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]13:26[/TD]
[TD="align: right"]38.00[/TD]
[TD="align: right"]13:44[/TD]
[TD="align: right"]22:15[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]13:27[/TD]
[TD="align: right"]38.00[/TD]
[TD="align: right"]13:51[/TD]
[TD="align: right"]20:58[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]13:28[/TD]
[TD="align: right"]38.00[/TD]
[TD="align: right"]13:55[/TD]
[TD="align: right"]18:58[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]13:29[/TD]
[TD="align: right"]38.00[/TD]
[TD="align: right"]14:01[/TD]
[TD="align: right"]21:24[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]13:30[/TD]
[TD="align: right"]38.00[/TD]
[TD="align: right"]13:59[/TD]
[TD="align: right"]20:22[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]13:31[/TD]
[TD="align: right"]38.00[/TD]
[TD="align: right"]14:25[/TD]
[TD="align: right"]20:06[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]13:32[/TD]
[TD="align: right"]38.00[/TD]
[TD="align: right"]14:29[/TD]
[TD="align: right"]21:24[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]13:33[/TD]
[TD="align: right"]39.00[/TD]
[TD="align: right"]15:01[/TD]
[TD="align: right"]22:08[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]13:34[/TD]
[TD="align: right"]39.00[/TD]
[TD="align: right"]15:24[/TD]
[TD="align: right"]21:11[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]13:35[/TD]
[TD="align: right"]39.00[/TD]
[TD="align: right"]15:23[/TD]
[TD="align: right"]21:28[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]13:36[/TD]
[TD="align: right"]39.00[/TD]
[TD="align: right"]15:31[/TD]
[TD="align: right"]20:53[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]13:37[/TD]
[TD="align: right"]39.00[/TD]
[TD="align: right"]15:40[/TD]
[TD="align: right"]22:51[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]13:38[/TD]
[TD="align: right"]39.00[/TD]
[TD="align: right"]15:42[/TD]
[TD="align: right"]23:39[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]13:39[/TD]
[TD="align: right"]40.00[/TD]
[TD="align: right"]15:50[/TD]
[TD="align: right"]0:06[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]13:40[/TD]
[TD="align: right"]39.00[/TD]
[TD="align: right"]15:57[/TD]
[TD="align: right"]20:33[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD="align: right"]13:41[/TD]
[TD="align: right"]39.00[/TD]
[TD="align: right"]15:56[/TD]
[TD="align: right"]23:26[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]13:42[/TD]
[TD="align: right"]39.00[/TD]
[TD="align: right"]16:03[/TD]
[TD="align: right"]22:40[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]13:43[/TD]
[TD="align: right"]39.00[/TD]
[TD="align: right"]16:40[/TD]
[TD="align: right"]0:54[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD="align: right"]13:44[/TD]
[TD="align: right"]40.00[/TD]
[TD="align: right"]16:52[/TD]
[TD="align: right"]0:16[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]13:45[/TD]
[TD="align: right"]40.00[/TD]
[TD="align: right"]17:01[/TD]
[TD="align: right"]22:25[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]13:46[/TD]
[TD="align: right"]40.00[/TD]
[TD="align: right"]17:01[/TD]
[TD="align: right"]23:37[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]13:47[/TD]
[TD="align: right"]40.00[/TD]
[TD="align: right"]17:11[/TD]
[TD="align: right"]0:27[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD="align: right"]13:48[/TD]
[TD="align: right"]40.00[/TD]
[TD="align: right"]17:12[/TD]
[TD="align: right"]0:46[/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD="align: right"]13:49[/TD]
[TD="align: right"]40.00[/TD]
[TD="align: right"]17:18[/TD]
[TD="align: right"]1:14[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD="align: right"]13:50[/TD]
[TD="align: right"]40.00[/TD]
[TD="align: right"]17:26[/TD]
[TD="align: right"]22:30[/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD="align: right"]13:51[/TD]
[TD="align: right"]41.00[/TD]
[TD="align: right"]17:32[/TD]
[TD="align: right"]23:21[/TD]
[/TR]
[/TABLE]

 

[FranzV]

Assuming you want to count the number of cells in column A that fall within the ranges of columns B and C, you need two formulas.

First you have to add one day to values on the Finish Column that fall the day after the Start Column. This has to be done because otherwise you will get an empty join out of these conditions. For example, if you want to count values that are greater than 16:40, but less than 0:54 you will always get 0, because no value greater than 16:40 will be less than 0:54.

This problem can be avoided easily if we add one day to all Finish values that are less than their corresponding Start value. Remember that Excel stores time values as fractions of a day, so 16:40 is actually 25/36, 0.694444445 or 0/Jan/1900 16:40 (depending on the display format), and 0:54 is 0.0375. The following table makes it clearer.

[TABLE="class: head"]
<tbody>[TR]
[TD]Value[/TD]
[TD]Time[/TD]
[TD]Fraction[/TD]
[TD]Decimal[/TD]
[TD]DateTime[/TD]
[/TR]
[TR]
[TD]START[/TD]
[TD]

16:40​

[/TD]
[TD]

25/36​

[/TD]
[TD]

0.694444444​

[/TD]
[TD]

00-ene-1900 16:40:00​

[/TD]
[/TR]
[TR]
[TD]FINISH[/TD]
[TD]

00:54​

[/TD]
[TD]

3/80​

[/TD]
[TD]

0.0375​

[/TD]
[TD]

00-ene-1900 00:54:00​

[/TD]
[/TR]
[TR]
[TD]NextDayFinish[/TD]
[TD]

00:54​

[/TD]
[TD]

1 3/80​

[/TD]
[TD]

1.0375​

[/TD]
[TD]

01-ene-1900 00:54:00​

[/TD]
[/TR]
</tbody>[/TABLE]

Therefore, it is necessary to add a helper column before we use a COUNTIFS function to get the desired result. I hope it helps.

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD]=E2+IF(E2<D2,1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G2[/TH]
[TD]=COUNTIFS($B$2:$B$30,">="&$D2,$B$2:$B$30,"<="&$E2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

	A	B	C	D	E	F	G
Minutes	Arrival Time	COUNT	START	FINISH	NextDayFinish	COUNTIFS

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]13:23[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]13:23[/TD]
[TD="align: right"]19:37[/TD]
[TD="align: right"]19:37[/TD]
[TD="align: right"]29[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]13:24[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]13:33[/TD]
[TD="align: right"]20:22[/TD]
[TD="align: right"]20:22[/TD]
[TD="align: right"]19[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13:25[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]13:39[/TD]
[TD="align: right"]21:24[/TD]
[TD="align: right"]21:24[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]13:26[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]13:44[/TD]
[TD="align: right"]22:15[/TD]
[TD="align: right"]22:15[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]13:27[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]13:51[/TD]
[TD="align: right"]20:58[/TD]
[TD="align: right"]20:58[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]13:28[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]13:55[/TD]
[TD="align: right"]18:58[/TD]
[TD="align: right"]18:58[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]13:29[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]14:01[/TD]
[TD="align: right"]21:24[/TD]
[TD="align: right"]21:24[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]13:30[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]13:59[/TD]
[TD="align: right"]20:22[/TD]
[TD="align: right"]20:22[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]13:31[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]14:25[/TD]
[TD="align: right"]20:06[/TD]
[TD="align: right"]20:06[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]13:32[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]14:29[/TD]
[TD="align: right"]21:24[/TD]
[TD="align: right"]21:24[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]13:33[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]15:01[/TD]
[TD="align: right"]22:08[/TD]
[TD="align: right"]22:08[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13:34[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]15:24[/TD]
[TD="align: right"]21:11[/TD]
[TD="align: right"]21:11[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]13:35[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]15:23[/TD]
[TD="align: right"]21:28[/TD]
[TD="align: right"]21:28[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]13:36[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]15:31[/TD]
[TD="align: right"]20:53[/TD]
[TD="align: right"]20:53[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]13:37[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]15:40[/TD]
[TD="align: right"]22:51[/TD]
[TD="align: right"]22:51[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]13:38[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]15:42[/TD]
[TD="align: right"]23:39[/TD]
[TD="align: right"]23:39[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]13:39[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]15:50[/TD]
[TD="align: right"]00:06[/TD]
[TD="align: right"]00:06[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]13:40[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]15:57[/TD]
[TD="align: right"]20:33[/TD]
[TD="align: right"]20:33[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]13:41[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]15:56[/TD]
[TD="align: right"]23:26[/TD]
[TD="align: right"]23:26[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]13:42[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]16:03[/TD]
[TD="align: right"]22:40[/TD]
[TD="align: right"]22:40[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]13:43[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]16:40[/TD]
[TD="align: right"]00:54[/TD]
[TD="align: right"]00:54[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]13:44[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]16:52[/TD]
[TD="align: right"]00:16[/TD]
[TD="align: right"]00:16[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]13:45[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]17:01[/TD]
[TD="align: right"]22:25[/TD]
[TD="align: right"]22:25[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]13:46[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]17:01[/TD]
[TD="align: right"]23:37[/TD]
[TD="align: right"]23:37[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]13:47[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]17:11[/TD]
[TD="align: right"]00:27[/TD]
[TD="align: right"]00:27[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]13:48[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]17:12[/TD]
[TD="align: right"]00:46[/TD]
[TD="align: right"]00:46[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]13:49[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]17:18[/TD]
[TD="align: right"]01:14[/TD]
[TD="align: right"]01:14[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]13:50[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]17:26[/TD]
[TD="align: right"]22:30[/TD]
[TD="align: right"]22:30[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]13:51[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]17:32[/TD]
[TD="align: right"]23:21[/TD]
[TD="align: right"]23:21[/TD]
[TD="align: right"]0
[/TD]

</tbody>

​