The AVERAGEIFS function returns the average, arithmetic mean, of all the cells in a range that meet multiple criteria.

  • This function was introduced in Excel 2007 and so is not available in earlier versions.

Syntax

=AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2], … )

Note: Beginning with Excel 2007, you can enter up to 255 number arguments to the function. Excel 2003 would only accept up to 30 number arguments.

Arguments

Argument Description
average_range One or more cells to average, including numbers or names, arrays, or references that contain numbers
criteria_range1,
[criteria_range2], …
Ranges of cells containing values to be tested against the respective criteria1criteria2, etc.

  The supplied criteria_range arrays must all have the same length
criteria1,
[criteria2], …
The conditions to be tested against the values in criteria_range1[criteria_range2], etc.

Note: The AVERAGEIFS function is not case-sensitive. So, for example, the text strings “TEXT” and “text” will be classed as equal.

Examples

  A B C D E F G
1 Quarter Region Agent Sales      
2 1 North Norton $222,100      
3 1 North Sam $252,280      
4 1 East Sarah $222,100      
5 2 North Norton $251,280      
6 2 South Sam $229,785      
7 2 East Sarah $232,190      
8 3 North Norton $294,820      
9 3 South Sam $273,139      
10 3 East Sarah $219,167      
11 4 North Norton $258,142      
12 4 South Sam $265,180      
13 4 East Sarah $266,132      
14              
15 Formula Result Notes
16 =AVERAGEIFS(D2:D13,A2:A13,1,B2:B13,”North”) $273,190 Average sales in the North region during the 1st Quarter

Usage note: You can also use the following wildcards in text matches for the criteria argument:

      ?  –  matches any single character
  *  –  matches any sequence of characters

e.g. the condition “A*e” will match any substring beginning with “A” and ending in “e”.

  • If you do actually want to find the ? or * character, type the ~ symbol before this character in your search.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if the length of any of the supplied criteria_range arrays is not equal to the length of the average_range array
#DIV/0! Occurs if either:

  none of the values in the supplied range array satisfy the supplied criteria
  the values to be averaged are all non-numeric

The AVERAGE function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. The three most common measures of central tendency are:

  • Average – the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers.
  • Median – the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median.
  • Mode – the most frequently occurring number in a group of numbers.