For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  



Returns the average (arithmetic mean) of all the cells in a range that meet one criteria.

by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports


AVERAGEIF(range, criteria, average_range)

  • range  Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers.
  • criteria  Required. The criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 17, "17", ">17", "hats", or B4.
  • average_range  Optional. The actual set of cells to average. If omitted, range is used.

Applies To

Excel 2007 and above


The following examples illustrate the common exceptions and limitations of this function.

Note that since the arguments for the AVERAGEIFS function are different from this function, which can cause confusion. But since the AVERAGEIFS function also can be limited to one criteria, I recommend that you always use the AVERAGEIFS function rather than this one.


You can download this example workbook here, along with all other example workbooks I've completed for this Excel help area.

Example1 for Excel's AVERAGEIF function

The average of sales values greater than 10.

Example2 for Excel's AVERAGEIF function

The average of the two South values.

Example3 for Excel's AVERAGEIF function

Empty cells and text values are ignored

Example4 for Excel's AVERAGEIF function

An error is returned when no values meet the Criteria

Example5 for Excel's AVERAGEIF function

You can use the "*" wildcard character.

Example6 for Excel's AVERAGEIF function

You can use the "?" wildcard charcter by itself, or in combination with the "*" character.

Example7 for Excel's AVERAGEIF function

The average_range (specified as cell D3 in the third argument) expands automatically to fit the dimensions of the Areas range, specified in the first argument.

Other Help


Charley's SwipeFile charts

Free Excel Dashboards