To average cells based on multiple criteria in Excel 2007 you use the AVERAGEIFS function.
Average_range is the range of cells to average, including numbers or names, arrays, or references that contain numbers.
Criteria_range1, criteria_range2, … are 1 to 127 ranges in which to evaluate the associated criteria.
Criteria1, criteria2, … are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged.
In this example we will average the total sales for Jim in quarter 2 (olive green rows). So our criteria are:
- “Jim” in column B.
- “Q2” in column C.
Our formula will be like this:
This formula returns a value of 1094, which is (2540+1250+178+408)/4
- If average_range is a blank or text value, AVERAGEIFS returns the #DIV0! error value.
- If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value.
- Each criteria_range must be the same size and shape as sum_range.
- If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value.