COUNTIFS (Needing to find value of a range between 1 time and another for a specific date)

[gooselfc]

I need to calculate the in E2 the value if range A:A is on a specific date (Using D2,D3 and so on), and also if the start time in B is greater than the header and if end time is less than the next time slot, basically count how many times in a 30 min segment would it fill on any specific day in the table from E onwards.

So from the data set in the image I would expect for E2 at 07:00 to have "6" (Times on the 17th starting on or before 07:00 counted in the segment) and F2 at 07:30 to have "9" (Times on the 17th starting on or before 07:30 counted in the segment) and so on...
Am I doing something completely wrong or am I missing something

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=COUNTIFS($A:$A,$D2,$B:$B,">="&E$1,$C:$C,"<="&F$1)

 

[keef2]

This should work. I think it is the formatting of your dates.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Start	End	Day	7:00 AM	7:30 AM	8:00 AM	8:30 AM	9:00 AM	9:30 AM	10:00 AM	10:30 AM	11:00 AM	11:30 AM	12:00 PM	12:30 PM	1:00 PM
2	10/17/2022	7:30		10/17/2022	6	3	0	1	1	1	2	0	0	0	0	0	0
3	10/17/2022	9:30		10/18/2022	3	2	1	0	0	1	0	0	0	0	0	0	0
4	10/17/2022	7:30
5	10/17/2022	7:00
6	10/17/2022	7:00
7	10/17/2022	10:00
8	10/17/2022	7:30
9	10/17/2022	10:00
10	10/17/2022	7:00
11	10/17/2022	8:30
12	10/17/2022	9:00
13	10/17/2022	7:00
14	10/17/2022	7:00
15	10/17/2022	7:00
16	10/18/2022	7:30
17	10/18/2022	9:30
18	10/18/2022	7:30
19	10/18/2022	7:00
20	10/18/2022	8:00
21	10/18/2022	7:00
22	10/18/2022	7:00
Sheet1

Cell Formulas
Range	Formula
F1:Q1	F1	=E1+TIME(,30,)
E2:Q3	E2	=COUNTIFS($A:$A,$D2,$B:$B,E$1)