# [SUMIFS] Counting number of occurrence within a time period

#### nocturne

##### New Member
Data

 START END VEHICLES REQUIRED JOB 1 1:00 2:10 2 JOB 2 1:00 2:10 3 JOB 3 0:55 2:05 2 JOB 4 0:35 1:45 2 JOB 5 0:25 1:35 2 JOB 6 0:20 1:30 2 JOB 7 0:00 1:10 1

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

How many vehicles required at any given time?

 0:00 0:01 0:02 0:03 0:04 0:05 0:20 0:25 Vehicles required 1 1 1 1 1 1 3 5

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

### 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
you were almost there

#### 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

</tbody>
Sheet3

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

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

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