For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  

Statistical

AVERAGEIFS Function

Returns the average of all cells that meet multiple criteria.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
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.

Example1 for Excel's AVERAGEIFS function

Only one number satisfies both criteria, the value shown.

Example1 for Excel's AVERAGEIFS function

Because all numbers meet these criteria, the formula returns the average of all three values.

Example1 for Excel's AVERAGEIFS function

The Sales range is referenced twice, once to specify the values to average and once to specify the criteria range to test.

Example1 for Excel's AVERAGEIFS function

Only the final number has a value greater than 10 and satisfies the second criteria.

Example1 for Excel's AVERAGEIFS function

AVERAGEIFS can average multiple rows and columns, if their shape is identical, as these are.

Example1 for Excel's AVERAGEIFS function

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

 

Charley's SwipeFile charts


Free Excel Dashboards