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 criteria1, criteria2, etc.


criteria1, [criteria2], … 
The conditions to be tested against the values in criteria_range1, [criteria_range2], etc. 
Note: The AVERAGEIFS function is not casesensitive. 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:

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.