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
Related Tutorials
AVERAGEIF with Criteria
Learn how to use AVERAGEIF with criteria in Excel and Google Sheets: syntax, text, number and date conditions, wildcards, and when to switch to AVERAGEIFS.
AVERAGEIFS Multiple Criteria
AVERAGEIFS multiple criteria lets you average cells that satisfy two or more conditions at once. Learn the syntax, real examples, and how it differs from AVERAGEIF and SUMIFS.