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.

Make Better Decisions
With Data

Analyze data, automate reports and create live dashboards
for all your business applications, without code. Get unlimited access free for 14 days.