Average If only picking up 1st of two criterias

[afelice52183]

Hello .. Been struggling with this formula =AVERAGEIFS(Tasks!$G:$G,Tasks!$A:$A,{"Northeast","NYC"},Tasks!$I:$I,"*Charter Legal*",Tasks!$H:$H,"Completed")


The formula is averaging correctly but only for Northeast not NYC any help would be greatly appreciated

 

[anglais428]

Wrap another "Average" at the start of your formula and add another closed bracket at the end:
=AVERAGE(AVERAGEIFS(Tasks!$G:$G,Tasks!$A:$A,{"Northeast","NYC"},Tasks!$I:$I,"*Charter Legal*",Tasks!$H:$H,"Completed"))

 

[afelice52183]

Wrap another "Average" at the start of your formula and add another closed bracket at the end:
=AVERAGE(AVERAGEIFS(Tasks!$G:$G,Tasks!$A:$A,{"Northeast","NYC"},Tasks!$I:$I,"*Charter Legal*",Tasks!$H:$H,"Completed"))

OMG you are a master.thank you.. So my AVG should reflect 7.50 and its reporting 8.50? Any idea my lord I need a bottle of wine already

 

[anglais428]

Can you post some sample data?

E.g.

NE	100
SE	20
NE	30
SE	40
NW	50

<tbody>
</tbody>

=AVERAGE(AVERAGEIFS(B1:B5,A1:A5,{"NE","SE"})) gives 47.5 which is (100+20+30+40)/4.

 

[afelice52183]

OMG it wont let me post attachments


-- removed inline image ---



-- removed inline image ---

 

[afelice52183]

Region	Region	Division	Created Date	Last Modified Date	In flight Task Interval (Days)	Completed Task Interval (Days)	Status	Subject
Northeast						10	Completed	Charter Legal
Northeast						10	Completed	Charter Legal
Northeast						5	Completed	Charter Legal
Northeast						4	Completed	Charter Legal
Northeast						3	Completed	Charter Legal
Northeast						10	Completed	Charter Legal
NYC						10	Completed	Charter Legal
NYC						10	Completed	Charter Legal

<tbody>
</tbody>

Northeast / NYC	Total	Average Age
Charter Legal Open	4	7
Charter Legal Completed	8	8.500
Legal Review Open
Legal Review Complete
COI Open
COI Completed

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[anglais428]

Assuming your table above starts in cell A1:

=AVERAGE(IF((H2:H9="Completed")*(I2:I9="Charter Legal")*(A2:A9={"Northeast","NYC"}),G2:G9))

Confirm with CTRL SHIFT ENTER as this is an array formula. It will give 7.75