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

#### Afomaci

##### New Member
[FONT=&quot]Hi, please i have three columns
Data is arranged as follows[/FONT]

 Arrival Time Start Finish Count 13:23 14:24 19:50 13:23 14:24 19:50 13:24 13:05 17:20 13:25 12:12 22:22 : : : : 13:22
[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]

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

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### FranzV

##### Board Regular
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.

 Value Time Fraction Decimal DateTime START 16:40​ 25/36​ 0.694444444​ 00-ene-1900 16:40:00​ FINISH 00:54​ 3/80​ 0.0375​ 00-ene-1900 00:54:00​ NextDayFinish 00:54​ 1 3/80​ 1.0375​ 01-ene-1900 00:54:00​

<tbody>
</tbody>

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

Worksheet Formulas
CellFormula
F2=E2+IF(E2<D2,1)
G2=COUNTIFS(\$B\$2:\$B\$30,">="&\$D2,\$B\$2:\$B\$30,"<="&\$E2)

</tbody>

<tbody>
</tbody>

ABCDEFG
1MinutesArrival TimeCOUNTSTARTFINISHNextDayFinishCOUNTIFS
2113:233813:2319:3719:3729
3213:243813:3320:2220:2219
4313:253813:3921:2421:2413
5413:263813:4422:1522:158
6513:273813:5120:5820:581
7613:283813:5518:5818:580
8713:293814:0121:2421:240
9813:303813:5920:2220:220
10913:313814:2520:0620:060
111013:323814:2921:2421:240
121113:333915:0122:0822:080
131213:343915:2421:1121:110
141313:353915:2321:2821:280
151413:363915:3120:5320:530
161513:373915:4022:5122:510
171613:383915:4223:3923:390
181713:394015:5000:0600:060
191813:403915:5720:3320:330
201913:413915:5623:2623:260
212013:423916:0322:4022:400
222113:433916:4000:5400:540
232213:444016:5200:1600:160
242313:454017:0122:2522:250
252413:464017:0123:3723:370
262513:474017:1100:2700:270
272613:484017:1200:4600:460
282713:494017:1801:1401:140
292813:504017:2622:3022:300
302913:514117:3223:2123:210

</tbody>

Replies
2
Views
434
Replies
12
Views
574
Replies
18
Views
667
Replies
9
Views
320
Replies
1
Views
251

1,171,627
Messages
5,876,522
Members
433,199
Latest member
guerin47

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back