[SUMIFS] Counting number of occurrence within a time period

[nocturne]

Data

[TABLE="width: 433"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD="align: center"]START[/TD]
[TD="align: center"]END[/TD]
[TD="align: center"]VEHICLES REQUIRED[/TD]
[/TR]
[TR]
[TD="align: center"]JOB 1[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]2:10[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]JOB 2[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]2:10[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]JOB 3[/TD]
[TD="align: center"]0:55[/TD]
[TD="align: center"]2:05[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]JOB 4[/TD]
[TD="align: center"]0:35[/TD]
[TD="align: center"]1:45[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]JOB 5[/TD]
[TD="align: center"]0:25[/TD]
[TD="align: center"]1:35[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]JOB 6[/TD]
[TD="align: center"]0:20[/TD]
[TD="align: center"]1:30[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]JOB 7[/TD]
[TD="align: center"]0:00[/TD]
[TD="align: center"]1:10[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Required end product (Sheet2)

How many vehicles required at any given time?

[TABLE="width: 540"]
<colgroup><col><col><col span="10"></colgroup><tbody>[TR]
[TD] [/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]0:01[/TD]
[TD="align: right"]0:02[/TD]
[TD="align: right"]0:03[/TD]
[TD="align: right"]0:04[/TD]
[TD="align: right"]0:05[/TD]
[TD="align: right"]0:20[/TD]
[TD="align: right"]0:25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Vehicles required[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]

[/TD]
[TD="align: right"]

[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

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]

you were almost there

Book1
	A	B	C	D	E	F	G	H	I
1		START	END	VEHICLES REQUIRED
2	JOB 1	01:00	02:10	2
3	JOB 2	01:00	02:10	3
4	JOB 3	00:55	02:05	2
5	JOB 4	00:35	01:45	2
6	JOB 5	00:25	01:35	2
7	JOB 6	00:20	01:30	2
8	JOB 7	00:00	01:10	1
9
10
11		00:00	00:01	00:02	00:03	00:04	00:05	00:20	00:25
12	Vehicles required	1	1	1	1	1	1	3	5
Sheet3

Cell Formulas
Range	Formula
B12	=SUMIFS($D$2:$D$8,$B$2:$B$8,"<="&B$11,$C$2:$C$8,">="&B$11)

 

[nocturne]

Thanks Alan. An extension of this query, i am unable to count if the time goes before Midnight.

you were almost there

	A	B	C	D	E	F	G	H	I
START	END	VEHICLES REQUIRED
JOB 1
JOB 2
JOB 3
JOB 4

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]23:35[/TD]
[TD="align: right"]00:10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]23:55[/TD]
[TD="align: right"]01:10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]00:10[/TD]
[TD="align: right"]02:05[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]00:20[/TD]
[TD="align: right"]01:45[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 987"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 975"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B12[/TH]
[TD]=SUMIFS($D$2:$D$8,$B$2:$B$8,"<="&B$11,$C$2:$C$8,">="&B$11)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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]

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.

[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD]START[/TD]
[TD]END[/TD]
[TD]VEHICLES REQUIRED[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]JOB 1[/TD]
[TD="align: right"]23:35[/TD]
[TD="align: right"]00:10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]JOB 2[/TD]
[TD="align: right"]23:55[/TD]
[TD="align: right"]01:10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]JOB 3[/TD]
[TD="align: right"]00:10[/TD]
[TD="align: right"]02:05[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]JOB 4[/TD]
[TD="align: right"]00:20[/TD]
[TD="align: right"]01:45[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="class: cms_table, width: 987"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="class: cms_table, width: 975"]
<tbody>[TR]
[TH="align: center"]Cell[/TH]
[TH="align: center"]Formula[/TH]
[/TR]
[TR]
[TH="align: center"]B12[/TH]
[TD]=SUMIFS($D$2:$D$8,$B$2:$B$8,"<="&B$11,$C$2:$C$8,">="&B$11)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

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

Book1
	A	B	C	D	E
1		START	END	VEHICLES REQUIRED
2	JOB 1	23:35	00:10	2	-0.017
3	JOB 2	23:55	01:10	3	-0.003
4	JOB 3	00:10	02:05	2	0.007
5	JOB 4	00:20	01:45	2	0.014
6	JOB 5
7	JOB 6
8	JOB 7
9
10
11		00:10	00:20
12	Vehicles required	5	4
Sheet1

Cell Formulas
Range	Formula
E2	=IF(B2>C2,B2-1,B2)
B12	=SUMIFS($D$2:$D$8,$E$2:$E$8,"<"&B$11,$C$2:$C$8,">="&B$11)