Returns the average based on a condition.


= AVERAGEIF(range, criteria, average_range)


  • range: A range that contains values from which you want to check the condition.
  • criteria_range: A range of cells from which you want to test the condition.
  • criteria: A condition that you want to mention.


  • If a range has no value or text, it will return #DIV/0!.
  • Cells in the range which contain TRUE or FALSE are ignored.
  • The size of the average_range and range should be the same.
  • Text and logical operators should be enclosed in double-quotes.
  • If you skip specifying the average_range, it will return the average of cells from range (if the range has numeric values) and, if the range has other than numeric values it will return #DIV/0!.


Written by Puneet for Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel for Mac