AVERAGEIFS function
AVERAGEIFS averages the values where every condition is true, such as Algebra scores from the fall term or passing scores only. The range to average comes first, then one or more range-and-criterion pairs, exactly like SUMIFS.
- Available in
- Every current version
- Returns
- The average of the values that meet all the conditions, or #DIV/0! if none do.
- Google Sheets
- Also available in Google Sheets.
Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
| Argument | Required? | What it is |
|---|---|---|
average_range | Required | The cells to average. |
criteria_range1 | Required | The first range to check. |
criteria1 | Required | The condition for the first range. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| G2 | =AVERAGEIFS(D2:D9,B2:B9,"Algebra",C2:C9,"Fall") | 72.67 | Two conditions on different columns |
| G3 | =AVERAGEIFS(D2:D9,B2:B9,"Algebra",D2:D9,">=60") | 79.67 | The averaged column can also be a condition |
| G4 | =AVERAGEIFS(D2:D9,A2:A9,"Lee") | 85.00 | One condition works too |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Student | Course | Term | Score | Check | Result | |
| Ana | Algebra | Fall | 88 | Algebra, fall | 72.67 | |
| Raj | Algebra | Spring | 54 | Algebra, passing only | 79.67 | |
| Lee | Biology | Fall | 91 | All of Lee's scores | 85.00 | |
| Kim | Algebra | Fall | 72 | |||
| Lee | Algebra | Spring | 79 | |||
| Mo | Biology | Spring | 66 | |||
| Ivy | Algebra | Fall | 58 | |||
| Lee | Biology | Spring | 85 |
Practice AVERAGEIFS
From lesson 8, COUNTIFS, SUMIFS & AVERAGEIFS. Graded instantly, then re-checked on changed data.
Conditional average
G7, average the revenue for the product in G3.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #DIV/0! | No row meets every condition. Loosen a condition or wrap the formula in IFERROR. |
| #VALUE! | The ranges are different sizes. Every range must cover the same rows. |
| Arguments in AVERAGEIF order | AVERAGEIFS puts the average range first; AVERAGEIF puts it last. |
Tips
- Point criteria at cells, like $B$2:$B$9,F2, to build a table of averages by group.
Questions
How do I average with multiple criteria?
Use AVERAGEIFS: =AVERAGEIFS(D2:D9,B2:B9,"Algebra",C2:C9,"Fall").
Does AVERAGEIFS ignore blank cells?
Yes. Blank cells in the average range are skipped.
Lessons that use AVERAGEIFS
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.