# Thread: [SUMIFS] Counting number of occurrence within a time period Thanks: 0 Likes: 0

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

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

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.

2. ## Re: [SUMIFS] Counting number of occurrence within a time period

you were almost there

ABCDEFGHI
1STARTENDVEHICLES REQUIRED
2JOB 101:0002:102
3JOB 201:0002:103
4JOB 300:5502:052
5JOB 400:3501:452
6JOB 500:2501:352
7JOB 600:2001:302
8JOB 700:0001:101
9
10
1100:0000:0100:0200:0300:0400:0500:2000:25
12Vehicles required11111135

Sheet3

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

3. ## Re: [SUMIFS] Counting number of occurrence within a time period

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

Originally Posted by AlanY
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
Sheet3

Worksheet Formulas
Cell Formula
B12 =SUMIFS(\$D\$2:\$D\$8,\$B\$2:\$B\$8,"<="&B\$11,\$C\$2:\$C\$8,">="&B\$11)
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.

4. ## Re: [SUMIFS] Counting number of occurrence within a time period

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

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

5. ## Re: [SUMIFS] Counting number of occurrence within a time period

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

ABCDE
1STARTENDVEHICLES REQUIRED
2JOB 123:3500:102-0.017
3JOB 223:5501:103-0.003
4JOB 300:1002:0520.007
5JOB 400:2001:4520.014
6JOB 5
7JOB 6
8JOB 7
9
10
1100:1000:20
12Vehicles required54

Sheet1

Worksheet Formulas
CellFormula
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)