AVERAGEIF Function
Averages values that satisfy one condition.
Syntax
Parameters
| Parameter | Description | Required |
|---|---|---|
| range | The range to evaluate against the criteria. | Required |
| criteria | The condition that determines which cells to average. | Required |
| average_range | The actual cells to average. If omitted, range is used. | Optional |
Basic Example
Average score for a specific class
=AVERAGEIF(A2:A100, "Class A", B2:B100)Averages values in B2:B100 only where A2:A100 is "Class A".
Advanced Examples
Example 1: Average above a threshold
Sales performanceAverage sales greater than 1000
=AVERAGEIF(B2:B100, ">1000")How AVERAGEIF Works
AVERAGEIF evaluates the criteria against each cell in range. For matching cells, it averages the corresponding value from average_range (or range itself if average_range is omitted).
Important Notes & Limitations
Only one criterion is supported.
Criteria ranges must match average_range in size.
Empty cells in average_range are ignored.
Common Errors & Fixes
#DIV/0! errorNo cells match the criteria.Fix: Check that the criteria is correct and that matching values exist.
Wrong averageText values in average_range are ignored.Fix: Convert text to numbers.
Download Practice File
AVERAGEIF
averageifPractice AVERAGEIF with Real Data
Download a sample CSV file with pre-populated data and practice exercises for the AVERAGEIF function. Works in both Excel and Google Sheets.
File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps