Returns the average based on multiple conditions.
= AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- criteria_range1: A range of cells from which you want to test the condition.
- criteria1: A condition that you want to test.
- [criteria_range2]: The second range of cells from which you want to test the condition.
- [criteria1]: A second condition that you want to test.
- If cells in criteria_range are blank/text value then it will return an error and if any cell from criteria_range or average range has TRUE or FALSE it will consider it as 1 and 0 respectively.
- You can also use logical operators and Excel’s wildcard characters in criteria.
- The size and shape of the average_range and each criteria_range should be the same.
- It will return #DIV/0! if there is no match found in criteria_range.
- Make sure to enclosed non-numeric values in double quotation marks.
Written by Puneet for Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel for Mac