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

Afomaci

New Member
Joined
Dec 30, 2016
Messages
3
[FONT=&quot]Hi, please i have three columns
Data is arranged as follows[/FONT]

Arrival TimeStartFinishCount
13:23 14:24 19:50
13:23 14:2419:50
13:24 13:0517:20
13:25 12:1222: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 MinutesArrival TimeCOUNTSTART FINISH
113:2338.0013:2319:37
213:2438.0013:3320:22
313:2538.0013:3921:24
413:2638.0013:4422:15
513:2738.0013:5120:58
613:2838.0013:5518:58
713:2938.0014:0121:24
813:3038.0013:5920:22
913:3138.0014:2520:06
1013:3238.0014:2921:24
1113:3339.0015:0122:08
1213:3439.0015:2421:11
1313:3539.0015:2321:28
1413:3639.0015:3120:53
1513:3739.0015:4022:51
1613:3839.0015:4223:39
1713:3940.0015:500:06
1813:4039.0015:5720:33
1913:4139.0015:5623:26
2013:4239.0016:0322:40
2113:4339.0016:400:54
2213:4440.0016:520:16
2313:4540.0017:0122:25
2413:4640.0017:0123:37
2513:4740.0017:110:27
2613:4840.0017:120:46
2713:4940.0017:181:14
2813:5040.0017:2622:30
2913:5141.0017:3223:21
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

FranzV

Board Regular
Joined
Dec 27, 2016
Messages
178
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.

ValueTimeFractionDecimalDateTime
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)

<thead>
</thead><tbody>
</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

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>



 

Watch MrExcel Video

Forum statistics

Threads
1,132,936
Messages
5,656,011
Members
418,264
Latest member
Reiper79

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top