Statistical
AVERAGEIFS Function
Returns the average of all cells that meet multiple criteria.
by
Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

The AVERAGEIFS function returns the average (the arithmetic mean) of all cells that meet one or more criteria.
Syntax
AVERAGEIFS(average_range,
criteria_range1,criteria1,
criteria_range2,criteria2...)
 average_range Required. One or
more cells to average
 criteria_range1, criteria_range2, … criteria_range1
is required, subsequent criteria_ranges are optional. You
have from 1 to
127 ranges in which to evaluate the associated criteria.
 criteria1, criteria2, ... criteria1
is required, subsequent criteria are optional. You have 1 to 127
criteria in the form of a number, expression, cell
reference, or text that define which cells will be averaged.
For example, criteria can be expressed as 17, "17", ">17",
"hats", or B4. The "*" and "?"
wildcards also are allowed.
Remarks
 If the average_range contains no numbers to average,
AVERAGEIFS returns the #DIV0! error value.
 If a cell in a criteria range is empty, AVERAGEIFS
treats it as a 0 value.
 TRUE evaluates as 1; FALSE evaluates as 0 (zero).
 Unlike the AVERAGEIF function, in AVERAGEIFS each
criteria_range must be the same size and shape as the
sum_range.
 If no cells meet all the criteria, AVERAGEIFS returns
the #DIV/0! error value.
 You can use the wildcard characters, question mark (?)
and asterisk (*), in the criteria. A question mark matches
any single character; an asterisk matches any sequence of
characters. If you want to find an actual question mark or
asterisk, type a tilde (~) before the character.
Applies To
Excel 2007 and above
Examples
You can
download this example workbook here,
along with all other example workbooks I've completed for this
Excel help area.
Only one number satisfies both
criteria, the value shown.
Because all numbers meet these
criteria, the formula returns the average of all three values.
The Sales range is referenced
twice, once to specify the values to average and once to specify
the criteria range to test.
Only the final number has a value
greater than 10 and satisfies the second criteria.
AVERAGEIFS can average multiple
rows and columns, if their shape is identical, as these are.
AVERAGEIFS can't average multiple
rows and columns if their shapes of all ranges aren't identical,
as shown here. The SUMPRODUCT
function offers more power, but takes longer to calculated.
Other Help
