[SUMIFS] Counting number of occurrence within a time period

nocturne

New Member
Joined
Feb 27, 2019
Messages
6
Data

STARTENDVEHICLES REQUIRED
JOB 11:002:102
JOB 21:002:103
JOB 30:552:052
JOB 40:351:452
JOB 50:251:352
JOB 60:201:302
JOB 70:001:101

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>
Required end product (Sheet2)

How many vehicles required at any given time?

0:000:010:020:030:040:050:200:25
Vehicles required11111135




<colgroup><col><col><col span="10"></colgroup><tbody>
</tbody>

Formula

=SUMIFS(D2:D8,B2:B8,'"<"&Sheet2!B1,C2:C8,">="&Sheet2!C1)

I cant quite get the right results. I hope i am making some sense. Appreciate the help.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
you were almost there

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">START</td><td style=";">END</td><td style=";">VEHICLES REQUIRED</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">JOB 1</td><td style="text-align: right;;">01:00</td><td style="text-align: right;;">02:10</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">JOB 2</td><td style="text-align: right;;">01:00</td><td style="text-align: right;;">02:10</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">JOB 3</td><td style="text-align: right;;">00:55</td><td style="text-align: right;;">02:05</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">JOB 4</td><td style="text-align: right;;">00:35</td><td style="text-align: right;;">01:45</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">JOB 5</td><td style="text-align: right;;">00:25</td><td style="text-align: right;;">01:35</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">JOB 6</td><td style="text-align: right;;">00:20</td><td style="text-align: right;;">01:30</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">JOB 7</td><td style="text-align: right;;">00:00</td><td style="text-align: right;;">01:10</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;">00:00</td><td style="text-align: right;;">00:01</td><td style="text-align: right;;">00:02</td><td style="text-align: right;;">00:03</td><td style="text-align: right;;">00:04</td><td style="text-align: right;;">00:05</td><td style="text-align: right;;">00:20</td><td style="text-align: right;;">00:25</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Vehicles required</td><td style="text-align: right;background-color: #E2EFDA;;">1</td><td style="text-align: right;background-color: #E2EFDA;;">1</td><td style="text-align: right;background-color: #E2EFDA;;">1</td><td style="text-align: right;background-color: #E2EFDA;;">1</td><td style="text-align: right;background-color: #E2EFDA;;">1</td><td style="text-align: right;background-color: #E2EFDA;;">1</td><td style="text-align: right;background-color: #E2EFDA;;">3</td><td style="text-align: right;background-color: #E2EFDA;;">5</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B12</th><td style="text-align:left">=SUMIFS(<font color="Blue">$D$2:$D$8,$B$2:$B$8,"<="&B$11,$C$2:$C$8,">="&B$11</font>)</td></tr></tbody></table></td></tr></table><br />
 

nocturne

New Member
Joined
Feb 27, 2019
Messages
6
Thanks Alan. An extension of this query, i am unable to count if the time goes before Midnight.

you were almost there

ABCDEFGHI
1STARTENDVEHICLES REQUIRED
2JOB 123:3500:102
3JOB 223:5501:103
4JOB 300:1002:052
5JOB 400:2001:452
6

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

Worksheet Formulas
CellFormula
B12=SUMIFS($D$2:$D$8,$B$2:$B$8,"<="&B$11,$C$2:$C$8,">="&B$11)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
Based on above data, using the same formula. It doesnt return a value of 5 for 00:00 till 00:09. It has probably got to do something with the time range. Please assist, thanks.
 

nocturne

New Member
Joined
Feb 27, 2019
Messages
6
Re-posting for clarity. Based on below data, using the same formula. It doesnt return a value of 5 for 00:00 but returns a value of 2 for 00:10. It has probably got to do something with the time range. Please assist, thanks.

ABCDE
1STARTENDVEHICLES REQUIRED
2JOB 123:3500:102
3JOB 223:5501:103
4JOB 300:1002:052
5JOB 400:2001:452

<tbody>
</tbody>



Worksheet Formulas
CellFormula
B12=SUMIFS($D$2:$D$8,$B$2:$B$8,"<="&B$11,$C$2:$C$8,">="&B$11)

<tbody>
</tbody>

<tbody>
</tbody>
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
well, as time only, no date for the calculations excel wouldn't know if the time is started the day before.
one way to work around it is to add an helper column, as in F

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">START</td><td style=";">END</td><td style=";">VEHICLES REQUIRED</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">JOB 1</td><td style="text-align: right;;">23:35</td><td style="text-align: right;;">00:10</td><td style="text-align: right;;">2</td><td style="text-align: right;;">-0.017</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">JOB 2</td><td style="text-align: right;;">23:55</td><td style="text-align: right;;">01:10</td><td style="text-align: right;;">3</td><td style="text-align: right;;">-0.003</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">JOB 3</td><td style="text-align: right;;">00:10</td><td style="text-align: right;;">02:05</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0.007</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">JOB 4</td><td style="text-align: right;;">00:20</td><td style="text-align: right;;">01:45</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0.014</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">JOB 5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">JOB 6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">JOB 7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;">00:10</td><td style="text-align: right;;">00:20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Vehicles required</td><td style="text-align: right;;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=IF(<font color="Blue">B2>C2,B2-1,B2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B12</th><td style="text-align:left">=SUMIFS(<font color="Blue">$D$2:$D$8,$E$2:$E$8,"<"&B$11,$C$2:$C$8,">="&B$11</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,102,682
Messages
5,488,239
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top