Skip to main content

AVERAGEIF

The AVERAGEIF function returns the arithmetic mean of the values that meet the given criterion.

This is a useful tool for analyzing data when you need to consider only those values that meet certain conditions.

Syntax​

AVERAGEIF(range, criterion, [average_range])

ArgumentDescriptionPermitted values
rangeCells that are checked for compliance with the criterion. They are used either to calculate the result or to select values from the averaging range (if specified)Cell range
criterionCondition in the form of a number, expression, or text that determines which cells to average
  • Equal to: “text”, 1, “=text”, or “=1”
  • Greater than: “>1”
  • Greater than or equal to: “>=1”
  • Less than: “<1”
  • Less than or equal to: “<=1”
  • Not equal to: “<>1” or “<>text”
  • Text with wildcards “?” and “*”.
[average_range](optional) Actual range for calculating the average valueRange of cells with numeric values

Examples of use​

Average value with one criterion

=AVERAGEIF(A1:A10, ">50")

Result: the average value in the range A1:A10 among values greater than 50.

Average value with text criterion

=AVERAGEIF(A1:A10, "Apple", B1:B10)

Result: the average value in the range B1:B10 for cells corresponding to cells in the range A1:A10 with the value “Apple”.

Average value based on the cell criterion

=AVERAGEIF(B1:B10, ">" & D1, C1:C10)

Result: the average value in the range C1:C10 for cells where the corresponding values in the range B1:B10 are greater than the value in cell D1.

Notes​

  • The AVERAGEIF function ignores empty cells and text values in the range, making it useful for working with incomplete data.
  • If none of the values meet the criteria, the function will return the #DIV/0! error.
  • You can use wildcards in the condition: “*” (any number of characters) and “?” (one character).