COUNTIFS Query

[Adam12345]

In column Wed 17, there are cell values showing military times from and to. I want to summarise the hours at the bottom of the column by the hour, for instance, how many times does the time period cover the hour of 0800 or between 0800-0859. The result needs to return 1 from the example below, for 0700 it needs to return 3, and so on.

Can anyone suggest a simple formula to extract this data from the range of cells?

Tried this formula =COUNTIF(I$10:I$301,">=1200*")-COUNTIF(I$10:I$301,">1300*"), but it only returns values starting with the first argument.

Many thanks

Adam

 

[arthurbr]

And where is col I please?

 

[Alex Blakenburg]

See if this is what you had in mind.

20230520 Count Overlapping Times Adam12345.xlsx
	H	I
1		Wed 17
2		1900-2400
3		2100-2400
4		0600-1500
5		0700-1100
6		1200-2030
7		1230-1800
8		1400-2230
9		0600-1500
10
11
12
13
14
15
16	Start of the Hour
17	0:00	0
18	1:00	0
19	3:00	0
20	4:00	0
21	5:00	0
22	6:00	2
23	7:00	3
24	8:00	3
25	9:00	3
26	10:00	3
27	11:00	2
28	12:00	3
29	13:00	4
30	14:00	5
31	15:00	3
32	16:00	3
33	17:00	3
34	18:00	2
35	19:00	2
36	20:00	1
37	21:00	1
38	22:00	0
39	23:00	0
Sheet1

Cell Formulas
Range	Formula
I17:I39	I17	=LET(startTime,TIME(LEFT($I$2:$I$9,2),MID($I$2:$I$9,3,2),0), endTime,TIME(MID($I$2:$I$9,6,2),RIGHT($I$2:$I$9,2),0), SUM((startTime<=H17)*(endTime>((H17+1/24)-0.00001))))

 

[Peter_SSs]

Welcome to the MrExcel board!

The result needs to return 1 from the example below,

Am I mis-understanding the data? To me, 0600-1500, 0700-1100 and 0600-1430 all cover the 0800 hour so the count would be 3 (as per Alex's count)?

Possibly this?

23 05 20.xlsm
	I	J	K	L
9			Hour	Count
10	1900-2400		0000	0
11	2100-2400		0100	0
12	0600-1500		0200	0
13	0700-1100		0300	0
14	1200-2030		0400	0
15	1230-1800		0500	0
16	1400-2230		0600	2
17	0600-1430		0700	3
18			0800	3
19			0900	3
20			1000	3
21			1100	2
22			1200	3
23			1300	4
24			1400	4
Count Hours

Cell Formulas
Range	Formula
L10:L24	L10	=LET(s,--LEFT(0&I$10:I$300,5),COUNT(FILTER(s,(s-K10<=0)*(RIGHT(0&I$10:I$300,4)-(K10+59)>=0),"")))