average ifs with average range in different lines / columns

[campelliann]

Hi,
Col1 Col 2 Col 3 Col 4 Col 5 Col 6
Name Attribute Grade Name Attribute Grade
Peter a 7 Peter a 9
Peter a 6 John d 8
John b 5 Mary e 7
Mary c 5 Mary f 6

Letīs say I want to do an average of Peter's "a" attribute with an averageifs (7,6,9), the average range ("Grade") is on two different columns (3, 6); also the criteria ranges "Name" and "Attribute".

Is it possible to have a criteria range on different lines/columns?

Thank you

[AliGW]

What's the reason for having the data laid out in this way?

[XOR LX]

Hi.

With your table in A1:F5 (with headers in row 1), your choice of name, e.g. "Peter", in I1 and attribute, e.g. "a", in J1:

=AVERAGEIFS(C2:F5,A2:D5,I1,B2:E5,J1)

Regards