Statistical

AVERAGEIF Function in Excel

Returns the average of cells that meet a single criterion.

Syntax

  • =AVERAGEIF(range, criteria, [average_range])

Arguments

  • range (required): Range to evaluate against criteria
  • criteria (required): Condition to match
  • average_range (optional): Actual cells to average (if different from range)

Examples

  • =AVERAGEIF(A:A, "East", B:B) - Average sales for East region - Result: $5,000
  • =AVERAGEIF(B:B, ">0") - Average of positive values only - Result: Positive average

AVERAGEIF best practices

  • Use AVERAGEIF when the average should include only records that match one condition.
  • Check for zeros versus blanks because zeros affect the average while blanks are ignored.

Common errors

  • #DIV/0! if no cells match criteria
  • Ignores empty cells and text in average_range

Use cases

  • Conditional averages
  • Filtered statistics
  • Performance metrics

Frequently asked questions

  • What is the difference between AVERAGE and AVERAGEIF? AVERAGE calculates the mean of all numbers in a range. AVERAGEIF only averages cells that meet a criterion. =AVERAGEIF(A:A,">0",B:B) averages B values only where corresponding A values are positive.
  • How do I average with multiple criteria? Use AVERAGEIFS: =AVERAGEIFS(C:C, A:A, "East", B:B, ">1000") averages C where A is East AND B>1000. AVERAGEIF only supports one criterion.
  • Why does AVERAGEIF return #DIV/0!? AVERAGEIF returns #DIV/0! when no cells match the criteria (can't average zero values). Wrap in IFERROR: =IFERROR(AVERAGEIF(...), 0) or check with COUNTIF first.
  • Does AVERAGEIF ignore text and blanks? Yes, AVERAGEIF ignores text and empty cells in the average_range. Only numeric values are averaged. Cells with 0 ARE included (they're numbers).
  • Can AVERAGEIF use wildcards? Yes! =AVERAGEIF(A:A, "*phone*", B:B) averages B values where A contains 'phone'. Use * for any characters, ? for single character.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.