[SUMIFS] Counting number of occurrence within a time period

nocturne

New Member
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.
 

AlanY

Well-known Member
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
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
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
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 />
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top