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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

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,102,274
Messages
5,485,782
Members
407,515
Latest member
franjey

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top