Counting times

[Littlesaz]

I have a column which gives you the absolute time difference between a planned delivery and the actual delivery. Payment depends on time bands. So if you were ten minutes late or ten minutes early, the column would be 00:10:00. I have approximately 20,000 rows, I need to count the number of entries that are

1. 10 mins and under
2. between 11 and 30
3. 31 and 60
4. over 60 minutes

What's the best way?

That's all folks

 

[coolman2001]

Try Countifs function.

 

[Littlesaz]

So what would be the syntax to count if the data was between 10 and 20 minutes for example. The data is formatted hh:mm:ss. I'm quite new to excel sorry.

 

[Littlesaz]

=COUNTIFS(T5:T768,">00:10:00",T5:T768,"<=00:30:00") Doesn't seem to work

Not sure what's wrong with this

 

[VoG]

Try like this

Excel Workbook
	A	B	C	D
1	00:01		up to 10	10
2	00:02		11-30	19
3	00:03
4	00:04
5	00:05
6	00:06
7	00:07
8	00:08
9	00:09
10	00:10
11	00:11
12	00:12
13	00:13
14	00:14
15	00:15
16	00:16
17	00:17
18	00:18
19	00:19
20	00:20
21	00:21
22	00:22
23	00:23
24	00:24
25	00:25
26	00:26
27	00:27
28	00:28
29	00:29
30	00:30
31	00:31
32	00:32
Sheet3

 

[Littlesaz]

Almost there

=COUNTIF(U2:U765,"<=0:10") isn't picking up the 00:00 to 10 mins, it's undercounting them by 10 but no idea why the ten as there aren't ten of any of the entries. I've checked the range and that looks ok. The rest are working fine.

Thanks

 

[VoG]

Strange ... maybe

=COUNTIF(U2:U765,"<="&TIME(0,10,0))

 

[Littlesaz]

Thanks all, got there in the end.