Need to calculate averages based on multiple conditions? AVERAGEIFS filters your data and finds the mean in one formula. No helper columns, no complex nesting, just clean conditional averaging.
What Makes AVERAGEIFS Useful
AVERAGEIFS handles complex calculations simply:
- Multiple criteria – Test up to 127 different conditions at once
- Different columns – Each condition can check a different column
- Flexible operators – Use equals, greater than, less than, or text matching
- Performance analysis – Perfect for analyzing subsets of data
- Date filtering – Great for averaging values within specific time periods
The Syntax
=AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2], ...)The average_range comes first, then pairs of criteria ranges and their conditions.
Example 1: Regional Sales Average
Find the average sales for the West region:
| Product | Region | Sales |
|---|---|---|
| Laptop | West | $4,500 |
| Mouse | East | $250 |
| Keyboard | West | $800 |
| Monitor | West | $1,200 |
Formula: =AVERAGEIFS(C2:C5, B2:B5, "West")
Result: $2,166.67
Average column C where column B equals “West”. Three values: 4500, 800, and 1200.
Example 2: Multiple Conditions
Average sales for laptops in the West region only:
| Product | Region | Sales |
|---|---|---|
| Laptop | West | $4,500 |
| Mouse | West | $250 |
| Laptop | East | $4,200 |
| Laptop | West | $4,800 |
Formula: =AVERAGEIFS(C2:C5, A2:A5, "Laptop", B2:B5, "West")
Result: $4,650
Both conditions must be true. Product must be “Laptop” AND region must be “West”. Average of 4500 and 4800.
Example 3: Numeric Criteria
Average all sales over $1,000:
| Product | Region | Sales |
|---|---|---|
| Laptop | West | $4,500 |
| Mouse | East | $250 |
| Keyboard | West | $800 |
| Monitor | West | $1,200 |
Formula: =AVERAGEIFS(C2:C5, C2:C5, ">1000")
Result: $2,850
Put comparison operators in quotes. Averages 4500 and 1200 only. You can use >, <, >=, <=, or <>.
Example 4: Date Range Analysis
Average sales from January 2025:
| Date | Product | Sales |
|---|---|---|
| 1/5/2025 | Laptop | $4,500 |
| 12/28/2024 | Mouse | $250 |
| 1/15/2025 | Keyboard | $800 |
| 1/22/2025 | Monitor | $1,200 |
Formula: =AVERAGEIFS(C2:C5, A2:A5, ">=1/1/2025", A2:A5, "<2/1/2025")
Result: $2,166.67
Use the same range twice with different criteria to create a date range. Between January 1st and February 1st.
Example 5: Using Cell References
Average sales for a region specified in cell E1:
| Product | Region | Sales |
|---|---|---|
| Laptop | West | $4,500 |
| Mouse | East | $250 |
| Keyboard | West | $800 |
Formula: =AVERAGEIFS(C2:C4, B2:B4, E1)
Result: Depends on what’s in E1
No quotes needed when referencing another cell. This makes your formulas dynamic and reusable.
Example 6: Performance by Quarter and Status
Average scores for completed projects in Q1:
| Project | Quarter | Status | Score |
|---|---|---|---|
| Alpha | Q1 | Complete | 87 |
| Beta | Q1 | Pending | 92 |
| Gamma | Q2 | Complete | 84 |
| Delta | Q1 | Complete | 91 |
Formula: =AVERAGEIFS(D2:D5, B2:B5, "Q1", C2:C5, "Complete")
Result: 89
Only Q1 AND Complete projects. Average of 87 and 91.
Common Operators
| Operator | Meaning | Example |
|---|---|---|
| = | Equal to | “West” |
| > | Greater than | “>1000” |
| < | Less than | “<500” |
| >= | Greater than or equal | “>=1/1/2025” |
| <= | Less than or equal | “<=100” |
| <> | Not equal to | “<>East” |
Common Mistakes to Avoid
Wrong Range Order
- AVERAGEIFS has average_range first
- AVERAGEIF has it last
- Don’t mix them up
Mismatched Range Sizes
- All ranges must be the same size
- C2:C10 works with B2:B10
- C2:C10 doesn’t work with B2:B15
Zero vs Empty Cells
- Empty cells are excluded from the average
- Cells with 0 are included in the calculation
- This affects your results significantly
Quick Tips
- All ranges must be the same size
- Criteria ranges can be different columns
- Put operators and dates in quotes
- Cell references don’t need quotes
- Text criteria are case-insensitive
Start Using It
Open a spreadsheet with data that has categories or filters. Pick two or three conditions to test. Write an AVERAGEIFS formula. You’ll immediately see patterns in your data you missed before.
AVERAGEIFS turns complex analysis into simple formulas.
Questions about AVERAGEIFS? Want help with complex conditional averaging? Let’s connect.