The AVERAGEIF function in Excel allows you to calculate the average of cells that meet a specific condition. Instead of averaging an entire range, you can average only the values that match criteria such as a product name, department, score threshold, or date range.
This is one of the most useful functions for analyzing business data, sales reports, grades, budgets, and performance metrics.
In This Guide, You’ll Learn
- What the AVERAGEIF function does
- The syntax and arguments of AVERAGEIF
- How to average numbers based on one condition
- How to use text, numbers, and comparison operators in criteria
- How to average values from a separate range
- Common AVERAGEIF mistakes and how to fix them
- Real-world examples of AVERAGEIF in Excel
What Is the AVERAGEIF Function?
The AVERAGEIF function calculates the average of cells that meet a single condition.
AVERAGEIF Syntax
=AVERAGEIF(range, criteria, [average_range])Argument Definitions:
- range — The group of cells you want to evaluate
- criteria — The condition that determines which cells are counted
- range_average — Optional. The cells to average if different from range
The criteria can include:
- Text (like “West”)
- Numbers (like 50)
- Expressions (like “>=100”)
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> AVERAGEIF Function Data
Basic AVERAGEIF Example
Suppose you have the following sales data:
You want the average sales for Sarah.
Use:
=AVERAGEIF(A2:A6,"Sarah",B2:B6)Excel averages only Sarah’s sales values.
Result is 1300
Using Numbers as Criteria
You can average values based on numeric conditions.
Formula:
=AVERAGEIF(D2:D6,">80")Result is 91
Excel averages only 90, 88, and 95.
Using Comparison Operators in AVERAGEIF
You can use operators like:
| Operator | Meaning |
|---|---|
> | Greater than |
< | Less than |
>= | Greater than or equal to |
<= | Less than or equal to |
<> | Not equal to |
Examples
Average values greater than 100
=AVERAGEIF(A2:A10,">100")Average values less than or equal to 500
=AVERAGEIF(A2:A10,"<=500")Average values not equal to zero
=AVERAGEIF(A2:A10,"<>0")Using Cell References in Criteria
Instead of typing criteria directly into the formula, you can reference a cell.
Suppose cell I2 contains:
SarahFormula:
=AVERAGEIF(F2:F6,I2,G2:G6)This makes formulas more flexible and easier to update.
Averaging Values from Another Range
The optional average_range argument allows Excel to evaluate one range while averaging another.
Formula:
=AVERAGEIF(K2:K5,"IT",L2:L5)Excel checks the department column but averages the salary column.
AVERAGEIF with Text Criteria
Text criteria must usually be enclosed in quotation marks.
Example:
=AVERAGEIF(N2:N10,"Marketing",O2:O10)Excel averages values where the matching cell contains “Marketing”.
AVERAGEIF with Dates
You can also use dates as criteria.
Example:
=AVERAGEIF(Q2:Q11,">=1/4/2026",R2:R11)This averages values associated with dates on or after January 4, 2026.
Common AVERAGEIF Errors
Forgetting Quotation Marks
Incorrect:
=AVERAGEIF(A2:A10,>80)Correct:
=AVERAGEIF(A2:A10,">80")Ranges Are Different Sizes
If range and average_range do not align properly, the results may be incorrect.
Correct example:
=AVERAGEIF(A2:A10,"HR",B2:B10)Both ranges contain the same number of rows.
No Matching Values
If no cells meet the criteria, Excel returns:
#DIV/0!This happens because Excel cannot average zero matching values.
Real-World Uses for AVERAGEIF
- Sales reporting
- Education
- Budget Analysis
- HR Analytics
AVERAGEIF vs AVERAGEIFS
| Function | Purpose |
|---|---|
| AVERAGEIF | Average using one condition |
| AVERAGEIFS | Average using multiple conditions |
Use AVERAGEIF when only one condition is needed.
Use AVERAGEIFS when you need multiple criteria.
Tips for Using AVERAGEIF
- Keep ranges the same size
- Use cell references for flexible formulas
- Use comparison operators for dynamic filtering
- Double-check quotation marks around criteria
- Combine with tables for expandable reports
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> AVERAGEIF Function Data
