Formulas and functions > Functions from A to Z > AVERAGEIFS (average if conditions are true)

Syntax:

AVERAGEIFS(AverageRange, Range1, Criterion1 [, Range2, Criterion2 ...])

Description:

This returns the arithmetic mean of those values in a cell range that fulfill all of the specified criteria.

This function is similar to the AVERAGEIF function, but it allows you to specify multiple search ranges (including one criterion for each range).

Note: The order of the parameters is different here: In AVERAGEIF, AverageRange is the last parameter – while it is the first parameter in AVERAGEIFS.

AverageRange is the cell range that contains the values whose average is to be calculated.

Range1 is the first cell range to be checked for the specified criterion. This range must have the same dimensions as AverageRange.

Criterion1 is the condition that the values in Range1 have to fulfill to be included:

Use numbers or text (like "42" or "screws") to obtain the average of all cells that contain that value.

Use conditions (like ">10" or "<=5") to obtain the average of all cells that match the specified condition.

Note: Criteria always have to be enclosed within double quotation marks (").

Unlike the AVERAGEIF function, AVERAGEIFS allows you to specify multiple search ranges (including one criterion for each range). If you do so, only occurrences where all criteria are fulfilled are included in the calculation of the average.

Compatibility notes:

This function is not supported by the .xls file format (used in Microsoft Excel 2003 and earlier). If you save a document in this format, all calculations using this function will be replaced with their current result as a fixed value.

If you want to use this function, you should not save your document in the "Microsoft Excel 97-2003 (.xls)" file format, but choose one of the following formats instead:

▪"PlanMaker document (.pmdx or .pmd)"

▪or: "Microsoft Excel 2007-2021 (.xlsx)"

Example:

AVERAGEIFS(A1:A10, C1:C10, ">1", C1:C10, "<2")

This formula returns the average of only those cells in the cell range A1:A10 where the corresponding cell in the range C1:C10 is greater than 1 and less than 2.