# COUNTIFS, SUMIFS & AVERAGEIFS (Excel lesson 8 of 45)

> Filter by several conditions at once, driven by input cells.

Source: https://spreadsheetpractice.com/learn/countifs-sumifs-averageifs · Updated October 29, 2026

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Logic & conditions"
- **Time:** about 10 minutes, 3 graded exercises
- **Functions:** `SUMIFS`, `COUNTIFS`, `AVERAGEIFS`
- **Access:** free
- **Key formula:** `=SUMIFS(D2:D11,A2:A11,G2,B2:B11,G3)`: Sum range first, then pairs

## How it works

The -IFS functions take any number of range/criteria pairs and include only rows that meet all of them.

The argument order differs from SUMIF: `SUMIFS` puts the range to add *first*. Point criteria at input cells instead of typing "East", and one formula answers every combination.

## Syntax

| Formula | What it does |
|---|---|
| `=SUMIFS(D2:D11,A2:A11,G2,B2:B11,G3)` | Sum range first, then pairs |
| `=COUNTIFS(A2:A11,G2,C2:C11,">=50")` | Count with two conditions |
| `=AVERAGEIFS(D2:D11,B2:B11,G3)` | Average with conditions |

## Watch out for

- SUMIF has the sum range last; SUMIFS has it first. Mixing them up is the most common -IFS error.
- Every range in an -IFS function must be the same size.

## The exercises

Solve them in the live grid at [COUNTIFS, SUMIFS & AVERAGEIFS](https://spreadsheetpractice.com/learn/countifs-sumifs-averageifs). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Two-condition total.** In G5, total the revenue for the region in G2 and the product in G3. Reference the input cells.
2. **Count with a threshold.** In G6, count orders in the region from G2 with 50 or more units.
3. **Conditional average.** In G7, average the revenue for the product in G3.

Previous: [COUNTIF & SUMIF](https://spreadsheetpractice.com/learn/countif-sumif) · Next: [Conditional formatting with formulas](https://spreadsheetpractice.com/learn/conditional-formatting-formulas)

---

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