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.
=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.
|average_range||One or more cells to average, including numbers or names, arrays, or references that contain numbers|
|Ranges of cells containing values to be tested against the respective criteria1, criteria2, etc.
|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.
|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.