Average Based on a Single Criteria
The formulas in the following examples return an average based on a single criteria. Note that the formula in the first example uses the function AVERAGEIF, which is only available in Excel 2007 and later versions.
Using AVERAGEIF in Excel 2007 and Later Versions
The following formula returns the average for B2:B10, where the corresponding value in A2:A10 equals the value in D2...
=AVERAGEIF(A2:A10,D2,B2:B10)
Based on the sample data, the formulas return 80.
Sample Workbook: Download
Using an Array Formula
The following formula returns the average for B2:B10, where the corresponding value in A2:A10 equals the value in D2...
=AVERAGE(IF(A2:A10=D2,B2:B10))
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