The average of cells that meet multiple criteria.
Sample Usage
AVERAGEIFS(A1:A25 ,B1:B25,">10",C1:C25,"<20")
Syntax
AVERAGEIFS(range, criteria_range1, criteria1, criteria_range2, criteria2)
- range - The range to average.
- criteria_range1 - The range to check against criteria1.
- criteria1 - The pattern or test to apply to criteria_range1.
- criteria_range2 - [OPTIONAL] The range to check against criteria2.
- criteria2 - [OPTIONAL] The pattern or test to apply to criteria_range2.
Examples
AVERAGEIFS(A1:A25 ,B1:B25,">10",C1:C25,"<20") returns the average of the elements in the range A1:A25 whose corresponding elements in criteria_range1 and criteria_range2 meet all the specified conditions:
- Corresponding numbers in the B1:B25 range must be greater than 10.
- Corresponding numbers in the C1:C25 range must be less than 20.
Notes
- Cells in range that contain TRUE or FALSE evaluate to 1 or 0 respectively.
- Ensure that criteria1, criteria2,.. are in quotation marks.
- The range, criteria_range1, criteria_range2,.. must contain the same number of rows and columns.
- criteria can contain wildcard characters. Currently, the use of asterisk (*) is supported.
- An asterisk matches any sequence of characters.
- To match an actual asterisk, type a tilde (~) before the character. For example: (~*).