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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]NE[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]SE[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]NE[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]SE[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]NW[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

=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]

<tbody>
[TD="class: xl265"] Region
[/TD]
[TD="class: xl265, width: 87"]Region[/TD]
[TD="class: xl265, width: 87"]Division[/TD]
[TD="class: xl265, width: 87"]Created Date[/TD]
[TD="class: xl265, width: 87"]Last Modified Date[/TD]
[TD="class: xl265, width: 87"]In flight Task Interval (Days)[/TD]
[TD="class: xl265, width: 87"]Completed Task Interval (Days)[/TD]
[TD="class: xl265, width: 87"]Status[/TD]
[TD="class: xl265, width: 298"]Subject[/TD]

[TD="class: xl267"]Northeast[/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268, align: right"]10[/TD]
[TD="class: xl267"]Completed[/TD]
[TD="class: xl267"]Charter Legal[/TD]

[TD="class: xl267"]Northeast[/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268, align: right"]10[/TD]
[TD="class: xl267"]Completed[/TD]
[TD="class: xl267"]Charter Legal[/TD]

[TD="class: xl267"]Northeast[/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268, align: right"]5[/TD]
[TD="class: xl267"]Completed[/TD]
[TD="class: xl267"]Charter Legal[/TD]

[TD="class: xl267"]Northeast[/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268, align: right"]4[/TD]
[TD="class: xl267"]Completed[/TD]
[TD="class: xl267"]Charter Legal[/TD]

[TD="class: xl267"]Northeast[/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268, align: right"]3[/TD]
[TD="class: xl267"]Completed[/TD]
[TD="class: xl267"]Charter Legal[/TD]

[TD="class: xl267"]Northeast[/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268"][/TD]
[TD="class: xl268, align: right"]10[/TD]
[TD="class: xl267"]Completed[/TD]
[TD="class: xl267"]Charter Legal[/TD]

[TD="class: xl267"]NYC[/TD]

[TD="class: xl268, align: right"]10[/TD]
[TD="class: xl267"]Completed[/TD]
[TD="class: xl267"]Charter Legal[/TD]

[TD="class: xl267"]NYC[/TD]

[TD="class: xl268, align: right"]10[/TD]
[TD="class: xl267"]Completed[/TD]
[TD="class: xl267"]Charter Legal[/TD]

</tbody>

[TABLE="width: 318"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Northeast / NYC [/TD]
[TD]Total[/TD]
[TD]Average Age[/TD]
[/TR]
[TR]
[TD]Charter Legal Open [/TD]
[TD]4[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Charter Legal Completed [/TD]
[TD]8[/TD]
[TD]8.500[/TD]
[/TR]
[TR]
[TD]Legal Review Open[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Legal Review Complete [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]COI Open [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]COI Completed [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[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