Article updated on March 27, 2018

The AVERAGEIFS function returns the average of cell values that evaluates to TRUE for multiple criteria.

Formula in cell F6:

=AVERAGEIFS(D3:D8,B3:B8,F3,C3:C8,G3)

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

AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2] , [criteria2], ...)

The AVERAGEIFS function allows you to have up to 127 criteria and criteria_range arguments.

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.
[criteria_range2] Optional. You are allowed to have multiple criteria ranges.
[criteria2] Optional. You are allowed to have multiple criteria.

Comments

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.