AVERAGEIF

Formulas / AVERAGEIF
Calculate the average of numbers in a range that meet a specific criteria.
=AVERAGEIF(range,criteria,[average_range])
  • range - required, cell range
  • criteria - required, number, expression, or text used to filter the range
  • average_range - [OPTIONAL] cells to average. If missing, the range will be averaged

Examples

  • =AVERAGEIF(C5:C15,">0")

    The AVERAGEIF function can be used to calculate the average of cells in a range that meet a certain criteria. For example, this formula returns the average of the prices in C5:C15 if the prices are greater than $0.

  • =AVERAGEIF(D5:D15,">=2",C5:C15)

    The AVERAGEIF function can also be used to calculate the average of a range based on criteria from another range. For example, this formula returns the average number of bedrooms in C5:C15 if the bedrooms entered in the cells in D5:D15 are 2 or greater.

  • =AVERAGEIF(D5:D15,">=3",C5:C15)

    The AVERAGEIF function can also be used to calculate the average of a range based on criteria from another range. For example, this formula returns the average number of bedrooms in C5:C15 if the bedrooms entered in the cells in D5:D15 are 3 or greater.


Summary

The AVERAGEIF function is used to calculate the average of a range of cells that meet a certain criteria. It requires two arguments, range and criteria, while the average_range argument is optional. AVERAGEIF ignores empty cells and cells containing TRUE or FALSE values, and returns a #DIV/0! error if no cells meet the criteria or the range is blank or a text value.

  • AVERAGEIF averages numbers in a range from cells meeting a criteria, which may include dates, text, and numbers. It only applies one criterion.
  • AVERAGEIF does not include empty cells in the average and ignores logical values, returning a #DIV/0! error if no cells match the criteria.
  • Use the AVERAGEIFS function to apply several criteria.
  • AVERAGEIF can perform partial matches with wildcards (*,?) and logical operators (>,<,<>,=).

Frequently Asked Questions

What is the AVERAGEIF function?
The AVERAGEIF function is a formula that calculates the average of cells in a range based on a criteria. The function averages cells in a range and returns the average as an arithmetic mean.
What does AVERAGEIF require?
AVERAGEIF requires the following:
  • A range of cells
  • A criteria
How does AVERAGEIF treat empty cells?
AVERAGEIF ignores cells that contain TRUE or FALSE, and treats empty cells as having a 0 value. It also ignores empty cells that appear in the average_range.
What errors does AVERAGEIF throw?
If no cells in the range meet the criteria, AVERAGEIF throws a #DIV/0! error. AVERAGEIF also throws a #DIV/0! error when the range is a text value or blank.

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV