# AVERAGEIFS function in Excel

> AVERAGEIFS averages the values where every condition is true, such as Algebra scores from the fall term or passing scores only.

Source: https://spreadsheetpractice.com/functions/averageifs · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** AVERAGEIFS averages the values where every condition is true, such as Algebra scores from the fall term or passing scores only.
- **Syntax:** `=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)`
- **Most common use:** `=AVERAGEIFS(D2:D9,B2:B9,"Algebra",C2:C9,"Fall")` returns 72.67 (Two conditions on different columns)
- **Available in:** Every current version
- **Category:** [Conditional sum and count functions](https://spreadsheetpractice.com/functions/category/conditional)
- **Returns:** The average of the values that meet all the conditions, or #DIV/0! if none do.
- **Google Sheets:** Also available in Google Sheets.

## What it does

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.

## 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 was calculated by running the formula on the sample sheet.

| 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 |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Student | Course | Term | Score |  | Check | Result |
| 2 | Ana | Algebra | Fall | 88 |  | Algebra, fall | 72.67 |
| 3 | Raj | Algebra | Spring | 54 |  | Algebra, passing only | 79.67 |
| 4 | Lee | Biology | Fall | 91 |  | All of Lee's scores | 85.00 |
| 5 | Kim | Algebra | Fall | 72 |  |  |  |
| 6 | Lee | Algebra | Spring | 79 |  |  |  |
| 7 | Mo | Biology | Spring | 66 |  |  |  |
| 8 | Ivy | Algebra | Fall | 58 |  |  |  |
| 9 | Lee | Biology | Spring | 85 |  |  |  |

## 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.

## Related functions

- [AVERAGEIF](https://spreadsheetpractice.com/functions/averageif): AVERAGEIF averages only the values that meet one condition: the average order from one region, response times for chat tickets, scores above zero.
- [SUMIFS](https://spreadsheetpractice.com/functions/sumifs): SUMIFS adds the values where every condition is true: East region widget sales, February revenue, large orders from one customer.
- [COUNTIFS](https://spreadsheetpractice.com/functions/countifs): COUNTIFS counts the rows where every condition is true: orders that are from the East region and shipped, deals between $500 and $1,000.

## 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.

## Practice

A graded AVERAGEIFS exercise ("Conditional average") is on [the AVERAGEIFS page](https://spreadsheetpractice.com/functions/averageifs). It is checked instantly, then re-checked on changed data.

Lessons that use AVERAGEIFS:

- [Lesson 8: COUNTIFS, SUMIFS & AVERAGEIFS](https://spreadsheetpractice.com/learn/countifs-sumifs-averageifs)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
