Quick Navigation
AVERAGEIF Function
Summary
The AVERAGEIF function calculates the arithmetic mean of numbers in a specified range that satisfy a given condition. This powerful tool enables conditional averaging without complex array formulas or helper columns.
Syntax
AVERAGEIF(range, criteria, [average_range])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| range | Range |
Yes | The cells to test against criteria |
| criteria | Any |
Yes | The condition that determines which cells to average |
| average_range | Range |
No | The actual cells to average (optional) |
Using the AVERAGEIF Function
AVERAGEIF revolutionizes data analysis by allowing you to compute averages based on specific conditions directly within a single formula. Perfect for sales reports, performance metrics, financial summaries, and any dataset requiring conditional statistical analysis.
Common AVERAGEIF Examples
Average Commissions Below Threshold
=AVERAGEIF(B2:B5,"<23000")Calculates average of commissions less than 23,000 from real estate data (result: 14,000)
Average Property Values by Size
=AVERAGEIF(A2:A5,"<250000")Finds average property value under 250,000 (result: 150,000)
Regional Performance Analysis
=AVERAGEIF(A2:A6,"=*West",B2:B6)Averages profits for all Western regions using wildcards (result: 16,733.50)
Exclude Specific Categories
=AVERAGEIF(A2:A6,"<>*(New Office)",B2:B6)Averages profits excluding new office locations (result: 18,589)
Frequently Asked Questions
Common Errors and Solutions
#DIV/0!
Cause: No cells meet criteria or dividing by zero
Solution: Verify criteria matches data or use IFERROR wrapper
#VALUE!
Cause: Invalid range reference or criteria
Solution: Check range syntax and criteria format
Wrong average
Cause: Range is blank/text-only
Solution: Ensure range contains numeric values
Notes
- TRUE/FALSE values in range are ignored
- Empty criteria treated as 0
- Measures arithmetic mean (central tendency)
- Perfect companion to SUMIF and COUNTIF
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in: Excel 2003, Excel XP, Excel 2000
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+