AVERAGEIFS Function
Averages values that meet all of the specified conditions.
Syntax
Parameters
| Parameter | Description | Required |
|---|---|---|
| average_range | The range of cells to average. | Required |
| criteria_range1 | The range to evaluate with the first criteria. | Required |
| criteria1 | The first condition. | Required |
| criteria_range2, criteria2 | Additional range/criteria pairs (optional). | Optional |
Basic Example
Average sales in the East region
=AVERAGEIFS(B2:B100, A2:A100, "East")Averages B2:B100 where A2:A100 equals "East".
Advanced Examples
Example 1: Multiple criteria
Regional product performanceAverage Widget sales in the East region
=AVERAGEIFS(C2:C100, A2:A100, "East", B2:B100, "Widget")How AVERAGEIFS Works
AVERAGEIFS evaluates each criteria pair with AND logic. Rows that satisfy all conditions have their corresponding value in average_range included in the average.
Important Notes & Limitations
All criteria ranges must match average_range in size.
Only AND logic is supported.
If no cells match, returns #DIV/0!.
Common Errors & Fixes
#DIV/0! errorNo cells match all criteria.Fix: Loosen criteria or check data.
#VALUE! errorCriteria ranges have different dimensions.Fix: Make ranges the same size.
Download Practice File
AVERAGEIFS
averageifsPractice AVERAGEIFS with Real Data
Download a sample CSV file with pre-populated data and practice exercises for the AVERAGEIFS 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.