[SUMIFS] Counting number of occurrence within a time period

[nocturne]

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.

 

[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
1		START	END	VEHICLES REQUIRED
2	JOB 1	23:35	00:10	2
3	JOB 2	23:55	01:10	3
4	JOB 3	00:10	02:05	2
5	JOB 4	00:20	01:45	2
6

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

Sheet3​

Worksheet Formulas Cell Formula 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]

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.

A	B	C	D	E
1		START	END	VEHICLES REQUIRED
2	JOB 1	23:35	00:10	2
3	JOB 2	23:55	01:10	3
4	JOB 3	00:10	02:05	2
5	JOB 4	00:20	01:45	2

<tbody>
</tbody>

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

<tbody>
</tbody>

 

[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)