Quick Navigation
AVERAGEIFS Function
Summary
The AVERAGEIFS function calculates the arithmetic mean of cells within a specified range that satisfy multiple user-defined criteria simultaneously. This powerful statistical tool enables precise data analysis by filtering datasets based on complex conditions.
Syntax
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| average_range | Range |
Yes | Cells containing numbers to average across matching criteria |
| criteria_range1 | Range |
Yes | First range in which to test criteria1 |
| criteria1 | Any |
Yes | First criteria defining which records to include |
| criteria_range2 | Range |
No | Additional criteria ranges (up to 127 total) |
| criteria2 | Any |
No | Additional criteria (up to 127 total pairs) |
Using the AVERAGEIFS Function
AVERAGEIFS excels at multi-criteria averaging scenarios such as calculating average sales for specific regions and time periods, student grades meeting multiple performance thresholds, or financial metrics filtered by department and date range. Each criteria_range must match the dimensions of average_range exactly.
Common AVERAGEIFS Examples
Average Quiz Scores (70-90 Range)
=AVERAGEIFS(B2:B5, B2:B5, ">70", B2:B5, "<90")Calculates average of first quiz grades between 70 and 90, excluding non-numeric 'Incomplete' entries. Returns 80.5.
Real Estate Price Analysis
=AVERAGEIFS(B2:B7, C2:C7, "Bellevue", D2:D7, ">2", E2:E7, "Yes")Average home price in Bellevue with 3+ bedrooms and garage. Returns approximately 397,839.
Sales Performance Filter
=AVERAGEIFS(Sales, Region, "North", Date, ">1/1/2024", Amount, ">10000")Average sales amount for North region after Jan 1, 2024, exceeding $10K.
Frequently Asked Questions
Common Errors and Solutions
#DIV/0!
Cause: No cells meet all criteria OR average_range contains only text/blank cells
Solution: Verify criteria logic and ensure average_range contains numeric data
#VALUE!
Cause: criteria_range sizes don't match average_range dimensions
Solution: All ranges must have identical size and shape
Wrong criteria results
Cause: Empty criteria cells treated as 0
Solution: Use explicit criteria like '<>0' or specific text matches
Notes
- TRUE=1, FALSE=0 in criteria ranges
- criteria_range must exactly match average_range dimensions
- Maximum 127 criteria pairs supported
- Wildcards: ? (single char), * (sequence), ~? or ~* for literals
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Excel 365
Not available in: Excel 2003, Excel XP, Excel 2000, Excel 97
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+