Average Return Values Based on Two Conditions (Times)

[loveland517]

PLEASE HELP! Small business owner trying to understand where I can improve my response times!

In the table below, I am attempting to find the average of the hours based on time frames.
In another cell I would like to calculate the average hours these tickets have been unfulfilled if the time open (lets say from 7am till 3pm, then 3pm to 11pm).
So I understand I need a formula to return values based on the time range (which is two conditions *a start and end time*) that I am am searching for and then to average them - I attempted to use IF nested within AVERAGE but got really confused. Any chance I could get some guidance?

*if formula was applied to below: the 7am-3pm cell would only average "4" and the 3pm-11pm cell the formula should average "21+20+20+1+231+232+156 / 7"

Hours	Date	Time	Type
21​	10/4/2021	3:08:56 PM​	A
20​	10/4/2021	4:06:34 PM​	A
20​	10/9/2021	6:06:55 PM​	A
1​	10/10/2021	4:14:20 PM​	B
4​	10/13/2021	11:39:26 AM​	B
231​	10/16/2021	3:44:05 PM​	B
232​	10/16/2021	3:10:25 PM​	C
156​	10/19/2021	7:16:09 PM​	C

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Hours	Date	Time	Type
2	21	04/10/2021	3:08:56 PM	A	Early	4
3	20	04/10/2021	4:06:34 PM	A	Late	97.28571
4	20	09/10/2021	6:06:55 PM	A
5	1	10/10/2021	4:14:20 PM	B
6	4	13/10/2021	11:39:26 AM	B
7	231	16/10/2021	3:44:05 PM	B
8	232	16/10/2021	3:10:25 PM	C
9	156	19/10/2021	7:16:09 PM	C
Data

Cell Formulas
Range	Formula
F2	F2	=AVERAGEIFS(A:A,C:C,">="&TIME(7,0,0),C:C,"<"&TIME(15,0,0))
F3	F3	=AVERAGEIFS(A:A,C:C,">="&TIME(15,0,0),C:C,"<"&TIME(23,0,0))

 

[loveland517]

PERFECT! THANK YOU SO MUCH!

 

[Fluff]

You're welcome & thanks for the feedback.