ExcelMathIntermediate

AVERAGEIF

Returns the average (arithmetic mean) of all cells in a range that meet a given criteria.

Read the syntaxReview worked examplesOpen the spreadsheet app
=AVERAGEIF(range, criteria, [average_range])
Open Spreadsheet Explorer

This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.

What the function does

Overview

Returns the average (arithmetic mean) of all cells in a range that meet a given criteria.

The AVERAGEIF function allows you to calculate the average of numbers in a range based on a single condition. This is incredibly useful for analyzing subsets of data without manually filtering or sorting. For example, you might want to find the average sales for a specific product, or the average score for students who passed an exam. The `range` argument specifies the cells to apply the `criteria` against. The `criteria` defines the condition, which can be a number, expression, cell reference, or text string. The optional `ange` specifies the actual cells to average; if omitted, the `range` argument is used for averaging. Be mindful that `ange` must be the same size and shape as `range`, otherwise, Excel may return incorrect results or errors. Text criteria should be enclosed in double quotation marks, and wildcards (*, ?) can be used for partial matches.

Quick reference

Syntax

=AVERAGEIF(range, criteria, [average_range])

Inputs

Arguments

rangeThe range of cells that you want to evaluate by criteria.
Example: A2:A100
criteriaThe condition or criteria in the form of a number, expression, cell reference, or text that defines which cells will be averaged.
Example: ">50"
average_range (optional)The actual set of cells to average. If omitted, the 'range' is used.
Example: B2:B100

Formula patterns

Examples

1

Average Sales for a Specific Product

=AVERAGEIF(A2:A10, "Product X", B2:B10)
conditional averagesales data
2

Average Scores Above a Threshold

=AVERAGEIF(C2:C20, ">70")
gradesthresholdstatistical

Avoid these issues

Common Errors

1

#DIV/0!

Cause: No cells meet the specified criteria, or the 'average_range' contains no numbers.

Fix: Review your 'criteria' to ensure it correctly identifies matching cells. Check the 'average_range' for numeric values.

2

#VALUE!

Cause: The 'average_range' is not the same size and shape as the 'range' argument.

Fix: Ensure that 'range' and 'average_range' have identical dimensions (e.g., both are 10 rows by 1 column).

Platform support

Compatibility

Excel 2007+Google Sheets

Available in all modern versions of Excel.

Source: Microsoft Support

Common questions

Frequently Asked Questions

Returns the average (arithmetic mean) of all cells in a range that meet a given criteria.

range: The range of cells that you want to evaluate by criteria. criteria: The condition or criteria in the form of a number, expression, cell reference, or text that defines which cells will be averaged. ange: The actual set of cells to average. If omitted, the 'range' is used.

#DIV/0!: Review your 'criteria' to ensure it correctly identifies matching cells. Check the 'ange' for numeric values. #VALUE!: Ensure that 'range' and 'ange' have identical dimensions (e.g., both are 10 rows by 1 column).