average_range - Required. The range of cells to average. Only numeric values are included in the calculation.
criteria_range1 - Required. The first range to evaluate against criteria1. Must be the same size as average_range.
criteria1 - Required. The condition that defines which cells to average. Can be number, text, expression, or cell reference.
criteria_range2, criteria2 - Optional. Optional additional criteria pairs. Up to 127 criteria pairs supported for complex filtering.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Quarter | Sales | Average |
| 2 | East | Q1 | 50000 | |
| 3 | East | Q2 | 60000 | |
| 4 | West | Q1 | 45000 | |
| 5 | East Q1 | =AVERAGEIFS(C2:C4,A2:A4,"East",B2:B4,"Q1") 50000 |
Average sales for East region in Q1 only
Returns mean of values matching both conditions
Average values between start and end dates
Dynamic date filtering with cell references
Average A-grade scores with 3 or fewer attempts
Combines text and numeric criteria
Average sales for products containing "Phone" that are sold
Uses wildcards for flexible text matching
Calculate average sales by region and product category to identify high-performing combinations with this multi-criteria averaging function. Sales managers use this pattern to compare performance across geographic territories and product lines simultaneously. The Excel formula enables multi-dimensional analysis essential for strategic planning, resource allocation, and territory management. Perfect for identifying underperforming markets, optimizing inventory distribution, setting realistic targets, and making data-driven expansion decisions based on actual average performance metrics.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Category | Sales | West+Electronics Avg |
| 2 | West | Electronics | $75,000 | |
| 3 | West | Electronics | $85,000 | |
| 4 | East | Electronics | $65,000 | |
| 5 | Average: | =AVERAGEIFS(C2:C4,A2:A4,"West",B2:B4,"Electronics") $80,000 |
Track average student scores filtered by academic department and semester using this multi-criteria averaging function. Education administrators analyze grade trends across departments and time periods to identify curriculum strengths and areas needing improvement. The Excel formula handles academic data perfectly, enabling institutions to benchmark departmental performance, track semester-over-semester changes, identify high-achieving student cohorts, and make evidence-based decisions about resource allocation and teaching methodology improvements.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Department | Semester | Score | CS+Fall Avg |
| 2 | CS | Fall | 85 | |
| 3 | CS | Fall | 92 | |
| 4 | Math | Fall | 78 | |
| 5 | Avg Score: | =AVERAGEIFS(C2:C4,A2:A4,"CS",B2:B4,"Fall") 88.5 |
❌ The Problem:
✅ Solution:
=AVERAGEIFS(A1:A10, B1:B10, "criteria")All ranges in this multi-criteria averaging function must have the same dimensions. This function checks each row across all ranges simultaneously - if ranges are different sizes, Excel cannot determine which cells to compare. Always verify that average_range, criteria_range1, criteria_range2, etc., all have the exact same number of rows and columns for accurate multi-criteria averaging.
❌ The Problem:
✅ Solution:
=(AVERAGEIFS(Sales,Region,"East")+AVERAGEIFS(Sales,Region,"West"))/2This function applies AND logic - all criteria must be true. For OR logic (average if East OR West), use multiple functions combined with arithmetic. Or use <Link href="/formulas/sumproduct">SUMPRODUCT</Link> with conditional arrays for more complex OR scenarios. Each call evaluates its own set of conditions independently, then you combine results mathematically.
❌ The Problem:
✅ Solution:
=AVERAGEIFS(A1:A10, B1:B10, ">100")Always enclose comparison operators in quotes when using this multi-criteria averaging function. For dynamic thresholds with cell references, use concatenation: <code>=AVERAGEIFS(A1:A10, B1:B10, ">"&D1)</code>. This function requires operator strings to be properly formatted - quotes make the operator part of the criteria text that Excel can parse and evaluate correctly.
Master these AVERAGEIFS variants:
From basics to advanced - AI generates perfect formulas instantly.