Count the number of times a time value appears within a 15-minute time intervals

So guys, Im trying to find how many time the hh:mm(column a) occurs between the given range (15-minute intervals in column b and column c). I'm dealing specifically with time format.
column b value should be >= and column c should be <
I was playing around with countif and countifs functions, but I see that under range parameter, you can choose one column. I have my ranges in two columns as shown:

Intended result in column d is shown in BOLD.



0:00:00 0:00:00 0:15:00 2
0:10:00 0:15:00 0:30:00 3
0:20:00 0:30:00 0:45:00 5
0:20:00 0:45:00 1:00:00
0:20:00 1:00:00 1:15:00
0:30:00 1:15:00 1:30:00
0:30:00 1:30:00 1:45:00
0:30:00 1:45:00 2:00:00
0:40:00 2:00:00 2:15:00
0:40:00 2:15:00 2:30:00
1:05:00 2:30:00 2:45:00
1:05:00 2:45:00 3:00:00
1:40:00 3:00:00 3:15:00
1:45:00 3:15:00 3:30:00
1:45:00 3:30:00 3:45:00

So i played around with the following array formula:

{=SUM((HOUR($C$2:$C$17)=HOUR(K2))*(MINUTE($C$2:$C$17)>=MINUTE(K2))*(MINUTE($C$2:$C$17)<MINUTE(L2)))}
it gives correct results until the row where it has 1:45:00 - 2:00:00. I think because I'm using hour value of K2 which in this case it's 1 and it checks between 1:45:00 and 1:00:00.

how can I correct this?

thanks

Originally Posted by nflsales

=COUNTIFS($C$2:$C$16,">="&K2,$C$2:$C$16,"<"&L2)
Pls ignore the above one try this

Perfect! I have done such a big step and still no use!! Thanks for that.. you mind explaining the use of &before K2 and L2?

Now with this say I have something like below:

A 0:00:00 0:00:00 0:15:00
A 0:10:00 0:15:00 0:30:00
A 0:20:00 0:30:00 0:45:00
D 0:20:00 0:45:00 1:00:00
D 0:20:00 1:00:00 1:15:00
A 0:30:00 1:15:00 1:30:00
A 0:30:00 1:30:00 1:45:00
D 0:30:00 1:45:00 2:00:00
A 0:40:00 2:00:00 2:15:00
D 0:40:00 2:15:00 2:30:00
D 1:05:00 2:30:00 2:45:00
D 1:05:00 2:45:00 3:00:00
D 1:40:00 3:00:00 3:15:00
D 1:45:00 3:15:00 3:30:00
D 1:45:00 3:30:00 3:45:00

Now we know that number of operations in that given time period is 2, 3, 5 etc. How can I tabulate A and D so I get operations separated. So for example, 2 operations in the first time period will be divided in to A=2 D=0 and second block: total 3 opeartions; A=1 D=2 etc.

Thanks a lot

oh I'm using the formula below and it seems to work. Would be great if you can have a look at it:

so on a new column
=COUNTIFS($B$2:$B$17,"="&"A",$C$2:$C$17,">="&K2,$C$2:$C$17,"<"&L2)

do the same for 'D'

Thanks