sum the average count days based on conditions

[Imran Azam]

Hi All

Hope everyone is well

I have the below data and i am trying to answer the question : what is the overall average length of days for open status for each manager ALi and MAX.

	A	B	C	D
1	Manager	Start Date	End Date	status
2	Ali	15/02/2019	17/02/2019	Open
3	Ali	18/02/2019	19/02/2019	Closed
4	Ali	19/02/2019	21/02/2019	Closed
5	Ali	19/02/2019	19/02/2019	Open
6	Ali	18/02/2019	25/02/2019	Open
7	Ali	20/02/2019	20/02/2019	Open
8	Max	05/02/2019	07/02/2019	Closed
9	Max	04/02/2019	12/02/2019	Open
10	Max	14/02/2019	14/02/2019	Closed
11	Max	12/02/2019	20/02/2019	Closed

<tbody>
</tbody>

i would like to return something like Ali: 9 days Max: 8 days

I think the date difference between start and end date for each row news to be done first, and then condition the manager name and open status and then do the average sum. This is the logic i want to apply but cant seem to get it into a formula

Can this be done if so how?

thank you for any help provided

 

[cunningAce]

This should do it,

Book1
	A	B	C	D	E
1	Manager	Start Date	End Date	status	Days
2	Ali	15/02/2019	17/02/2019	Open	2
3	Ali	18/02/2019	19/02/2019	Closed	1
4	Ali	19/02/2019	21/02/2019	Closed	2
5	Ali	19/02/2019	19/02/2019	Open	0
6	Ali	18/02/2019	25/02/2019	Open	7
7	Ali	20/02/2019	20/02/2019	Open	0
8	Max	05/02/2019	07/02/2019	Closed	2
9	Max	04/02/2019	12/02/2019	Open	8
10	Max	14/02/2019	14/02/2019	Closed	0
11	Max	12/02/2019	20/02/2019	Closed	8
12
13	Ali	2.25
14	Max	8
Sheet1

Cell Formulas
Range	Formula
E2	=C2-B2
E3	=C3-B3
E4	=C4-B4
E5	=C5-B5
E6	=C6-B6
E7	=C7-B7
E8	=C8-B8
E9	=C9-B9
E10	=C10-B10
E11	=C11-B11
B13	=AVERAGEIFS($E$2:$E$11,$A$2:$A$11,A13,$D$2:$D$11,"Open")
B14	=AVERAGEIFS($E$2:$E$11,$A$2:$A$11,A14,$D$2:$D$11,"Open")