Average days to close between dates for specific person

[Resguy]

Hey,

I'm trying to create a formula that will calculate the average number of days for a ticket to be closed based on a particular person between 2 dates. I have a tab named Archived which has the closed ticket information on it. This includes the time to close per ticket. On Sheet 2, I have the total number of tickets closed by each individual. 

I am trying to create a formula that will search between 2 dates (i.e 01/08/2020 - 31/08/2020) for a specific persons name (i.e Joe Bloggs) to give a sum of how many days it took to  close all their tickets combined so that I can work out the average. Test data sheet uploaded. 

Any help would be appreciated. 

Thanks

[#2 · Accepted solution]

@Resguy 

In E4:

=IFERROR(AVERAGEIFS(Archived!$F$2:$F$15,Archived!$B$2:$B$15,M$1,Archived!$E$2:$E$15,">="&M4,Archived!$E$2:$E$15,"<="&N4),"-")

Copy down to E6, then copy to K4:K6 and to Q4:Q6

[#3]

@Hans Vogelaar Thank you for your help, this worked.