- Advanced Excel Functions Tutorial
- Advanced Excel Functions - Home
- Compatibility Functions
- Advanced Excel Functions - Cube
- Database Functions
- Date & Time Functions
- Engineering Functions
- Financial Functions
- Information Functions
- Advanced Excel Functions - Logical
- Lookup & Reference Functions
- Math & Trignometric Functions
- Statistical Functions
- Useful Resources
- Quick Guide
- Useful Resources
- Discussion
Statistical - AVERAGEIFS Function
Description
The AVERAGEIFS function returns the average (arithmetic mean) of all cells that meet multiple criteria.
Syntax
AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2] ...)
Arguments
Argument | Description | Required/ Optional |
---|---|---|
Average_range | One or more cells to average, including numbers or names, arrays, or references that contain numbers. | Required |
Criteria_range1 | 1 to 127 ranges in which to evaluate the associated criteria. | Required |
Criteria_range2,... | Optional | |
Criteria1 | 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged | Required |
criteria2, ... | Optional |
Notes
If average_range is a blank or text value, AVERAGEIFS returns the #DIV0! error value
If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value.
Cells in range that contain TRUE evaluate as 1. Cells in range that contain FALSE evaluate as 0 (zero).
Each cell in average_range is used in the average calculation only if all of the corresponding criteria specified are true for that cell.
You can use the wildcard characters, question mark (?) and asterisk (*), in 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.
Unlike the range and criteria arguments in the AVERAGEIF function, in AVERAGEIFS each criteria_range must be the same size and shape as average_range.
If the length of any of the supplied criteria_range arrays is not equal to the length of the average_range array, AVERAGEIFS returns the #VALUE! error value.
If cells in average_range cannot be translated into numbers, AVERAGEIFS returns the #DIV0! error value.
If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value.
Applicability
Excel 2007, Excel 2010, Excel 2013, Excel 2016