Average cells based on multiple criteria in Excel 2007

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:

  1. “Jim” in column B.
  2. “Q2″ in column C.

Our formula will be like this:


This formula returns a value of 1094, which is (2540+1250+178+408)/4



  1. If average_range is a blank or text value, AVERAGEIFS returns the #DIV0! error value.
  2. If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value.
  3. Each criteria_range must be the same size and shape as sum_range.
  4. If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value.