range - Required. Range to evaluate against criteria (e.g., B2:B100 for regions or categories).
criteria - Required. Condition to test - number, text, expression like ">70", "East", or cell reference.
average_range - Optional. Optional. Actual cells to average if different from range (e.g., sales values).
| A | B | C | |
|---|---|---|---|
| 1 | Product | Region | Sales |
| 2 | Laptop | East | 5000 |
| 3 | Mouse | West | 150 |
| 4 | Keyboard | East | 300 |
| 5 | Monitor | East | 800 |
| 6 | East Avg: | =AVERAGEIF(B2:B5, "East", C2:C5) 2033 |
Average sales for East region only
Calculates mean of C values where B="East"
Average of all scores 90 or above
Returns mean of values ≥90 in range A2:A100
Average price for category in A2
Dynamic criteria from cell reference
Average excluding zero values
Mean of non-zero numbers only
Use this conditional averaging function to calculate average sales by region for performance comparison. Sales managers use AVERAGEIF to benchmark territory performance - identify high-performing regions, spot underperforming areas, and allocate resources effectively. The AVERAGEIF formula makes regional analysis instant and accurate. This pattern applies to any scenario where you need category-based averages: department performance, product line profitability, customer segment analysis, or territory metrics across any business dimension.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Salesperson | Region | Sales | Region Avg |
| 2 | John | West | $45K | |
| 3 | Sarah | East | $52K | |
| 4 | Mike | West | $38K | |
| 5 | Lisa | East | $61K | |
| 6 | West Avg: | =AVERAGEIF(B2:B5, "West", C2:C5) $41.5K |
Calculate average scores for high performers using this conditional averaging function with comparison operators. Teachers use AVERAGEIF to segment student performance - track honors students separately, identify gifted learners, measure advanced program effectiveness. The AVERAGEIF formula with greater-than criteria isolates top performers instantly. Essential for grade distribution analysis, scholarship qualification, advanced placement recommendations, and academic performance reporting across schools and districts.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | High Avg (≥90) |
| 2 | Alice | 95 | |
| 3 | Bob | 82 | |
| 4 | Carol | 91 | |
| 5 | David | 88 | |
| 6 | Result: | =AVERAGEIF(B2:B5, ">=90") 93 |
❌ The Problem:
✅ Solution:
=AVERAGEIF(B2:B100, "East", C2:C100)Always specify the average_range (third parameter) when your criteria column differs from your values column. The AVERAGEIF formula needs to know which range to evaluate (B2:B100 for region) and which range to average (C2:C100 for sales). This three-parameter pattern is essential for most real-world scenarios where filtering criteria and numeric values live in separate columns.
❌ The Problem:
✅ Solution:
=AVERAGEIF(A1:A10, ">="&B1)Enclose operators in quotes and use & to concatenate with cell references: ">="&B1 combines the operator with cell B1's value. The AVERAGEIF formula requires proper string concatenation for dynamic comparison criteria. This pattern enables flexible thresholds that users can change without editing formulas - essential for interactive reports and dashboards.
❌ The Problem:
✅ Solution:
=(AVERAGEIF(Region, "East", Sales) + AVERAGEIF(Region, "West", Sales))/2For multiple criteria with OR logic, use multiple AVERAGEIF formulas and combine results. Or better, upgrade to <Link href="/formulas/averageifs">AVERAGEIFS</Link> for AND logic, or use array formulas for complex OR conditions. The AVERAGEIF function is designed for single-criterion filtering - multi-criteria scenarios require different techniques or functions.
Master these AVERAGEIF variants:
From basics to advanced - AI generates perfect formulas instantly.