Counting the number of times a time appears in a range of data

Hi guys,

I have tried to read through a fair few of the posts to see if I can find what I am looking for, but I don't seem to be able to (although once I have the answer, I'm sure I will have seen ot several times over in various guises!!)

I have a column of data in the same place on several different tabs in a workbook (column B - similar to the below), with different times in each cell.

16:47:16
12:48:12
14:55:20
15:45:44
11:25:56
11:26:45
13:35:02
13:39:01
09:23:23
10:42:12
11:07:36
11:03:05
14:25:39
12:35:04
13:05:31
14:44:09
11:27:43
10:45:26
11:50:03
14:43:12

What I am trying to work out, is how to count the number of occurences of a time in a timescale. I have broken the day into 15 min. intervals (column C - similar to the below).

What I am looking for, is a formula to look through all of column B & count up all the instances in the time range that is equal to or greater than point A & less than point B (E.g. 11:15 to 11:29:59 would be 3 from the above data.)

08:00
08:15
08:30
08:45
09:00
09:15
09:30
09:45
10:00
10:15
10:30
10:45
11:00
11:15
11:30
11:45
12:00
12:15
12:30
12:45


Thanks in advance & apologies if this is something that is always being asked - I appreciate it must be frustrating being asked the same questions!!

Alex.

Assuming your second list is in column J, put this formula in K1:

=COUNTIFS(B:B,">="&J1,B:B,"<"&J2)

then copy down.

Hope this helps.

Pete

Hi Pete,

Thank you for your help, but unfortunately I can't get the formula to work. I think I have uploaded a copy of what I am trying to do, but the formula is just returning a 0 value ...

Thanks.

Alex.

That is because all your "times" are actually text values that look like times. You can convert them quite easily by highlighting column B, then click on Data | Text-to-columns then click Finish on the first panel. Then your counts will appear in column G.

Hope this helps.

Pete

Excellent, thanks for your help Pete - really appreciate it.