Average Based on Multiple Criteria
The formulas in the following examples return an average based on multiple criteria. Note that the formula in the first example uses the function AVERAGEIFS, which is only available in Excel 2007 and later versions.
Using AVERAGEIFS in Excel 2007 and Later Versions
The following formula returns the average for C2:C10, where the corresponding value in A2:A10 equals the value in E2, and the corresponding value in B2:B10 equals the value in F2...
=AVERAGEIFS(C2:C10,A2:A10,E2,B2:B10,F2)
Based on the sample data, the formula returns 80.
Sample Workbook: Download
Using an Array Formula
The following array formula returns the average for C2:C10, where the corresponding value in A2:A10 equals the value in E2, and the corresponding value in B2:B10 equals the value in F2...
=AVERAGE(IF(A2:A10=E2,IF(B2:B10=F2,C2:C10)))
Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.
Sample Workbook: Download