|
<< Click to Display Table of Contents >> AVERAGEIFS (average if conditions are true) |
Syntax:
AVERAGEIFS(AverageRange, Range1, Criterion1 [, Range2, Criterion2 ...])
Description:
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 allows you to specify more than just one range/criterion to be searched.
Apart from that, the order of the parameters is different: In AVERAGEIF, AverageRange is the last parameter – in AVERAGEIFS, it is the first.
AverageRange is the cell range containing the values to be used for calculating the average.
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 "bolts") 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 surrounded by double quotation marks (").
Unlike the AVERAGEIF function, AVERAGEIFS allows you to specify more than just one cell range and criterion to be checked. 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 by their last result as a fixed value.
Accordingly, if you 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.
See also: