The AVERAGEIFS function returns the average of cell values that evaluates to TRUE for multiple criteria.
Formula in cell F6:
The formula in cell F6 in the picture above calculates the average for values that are in category "B" and size "Small". 3 and 2 meet the criteria and the average is 2.5.
Excel Function Syntax
average_range, criteria_range1, criteria1, [ criteria_range2] , [ criteria2], ...)
The AVERAGEIFS function allows you to have up to 127 criteria and criteria_range arguments.
average_range Required. One or more cells to average, including numbers or names, arrays, or references that contain numbers.
criteria_range1 Required. A cell range in which to evaluate the corresponding condition
criteria1 Required. A condition in the form of a cell reference, expression or text that determines which values to be averaged.
Optional. You are allowed to have multiple criteria ranges.
Optional. You are allowed to have multiple criteria.
Boolean values TRUE or FALSE are excluded in the calculation.
An empty cell in argument
average_range is excluded.
A blank or text value in argument range is not valid, the function returns #DIV0!.
A value is used in the calculation only if all of the corresponding criteria specified are true for that cell.
If a condition in argument average_range is empty is the same as 0 (zero).
The AVERAGEIFS function returns #DIV0! if no values match the criteria.
It is possible to use wildcard characters like (?) question mark or (*) asterisk.
(?) question mark matches any single character.
(*) asterisk matches any sequence of characters.
Use a tilde (~) before to escape the wildcard characters meaning finding the actual question mark or asterisk.
The AVERAGEIFS function function is one of many functions in the 'Statistical' category.