Related Functions:

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.