AVERAGE time to complete a job.

[surkdidat]

Hi

In Column J I have the job status (ie Must only include "Complete")
In Column H I have the workstream (Must only be "France")
In Column G I have the start date
In Column Q I have the completed date

What I need is IF it is COmplete AND France
Then for the Average time to complete all those jobs to be displayed

Thanks in advance

 

[JamesCanale]

MrExcelPlayground10.xlsx
	J	K	L	M	N	O
1	Status	Workstream	Start	End		Average
2	Complete	France	6/1/2022	6/5/2022		8.5
3	Complete	Not France	6/2/2022	6/7/2022
4	Incomplete	Not France	6/3/2022
5	Complete	France	6/4/2022	6/11/2022
6	Complete	France	6/5/2022	6/13/2022
7	Incomplete	France	6/6/2022
8	Incomplete	Not France	6/7/2022
9	Incomplete	France	6/8/2022
10	Complete	Not France	6/9/2022	6/21/2022
11	Incomplete	Not France	6/10/2022
12	Incomplete	Not France	6/11/2022
13	Complete	France	6/12/2022	6/27/2022
Sheet21

Cell Formulas
Range	Formula
O2	O2	=(SUMIFS(M2:M13,J2:J13,"Complete",K2:K13,"France")-SUMIFS(L2:L13,J2:J13,"Complete",K2:K13,"France"))/COUNTIFS(J2:J13,"Complete",K2:K13,"France")

 

[surkdidat]

=(SUMIFS(M2:M13,J2:J13,"Complete",K2:K13,"France")-SUMIFS(L2:L13,J2:J13,"Complete",K2:K13,"France"))/COUNTIFS(J2:J13,"Complete",K2:K13,"France")

Thank you for this. I did omit that the start and end date should both be included. So if the start date was 1 June, and the end date was 2 June this should be 2 days.
It also needs to discount weekends.

As a bonus I do have a date range (Start date A1, End Date B1) - Which I want to use on the "Completed" Date only. (The start date is irrelevant)