# sum the average count days based on conditions

#### Imran Azam

##### Board Regular
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

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### cunningAce

##### Board Regular
This should do it,

Book1
ABCDE
1ManagerStart DateEnd DatestatusDays
2Ali15/02/201917/02/2019Open2
3Ali18/02/201919/02/2019Closed1
4Ali19/02/201921/02/2019Closed2
5Ali19/02/201919/02/2019Open0
6Ali18/02/201925/02/2019Open7
7Ali20/02/201920/02/2019Open0
8Max05/02/201907/02/2019Closed2
9Max04/02/201912/02/2019Open8
10Max14/02/201914/02/2019Closed0
11Max12/02/201920/02/2019Closed8
12
13Ali2.25
14Max8
Sheet1
Cell Formulas
RangeFormula
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")

Replies
5
Views
142
Replies
12
Views
233
Replies
4
Views
104
Replies
0
Views
168
Replies
1
Views
177

1,195,992
Messages
6,012,739
Members
441,724
Latest member
Aalbid

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back