# FILTER (Excel lesson 27 of 45)

> Return only the rows that pass a test, and keep them live.

Source: https://spreadsheetpractice.com/learn/filter-function · Updated October 29, 2026

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Dynamic arrays"
- **Time:** about 10 minutes, 3 graded exercises
- **Functions:** `FILTER`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=FILTER(A2:D11,C2:C11=G2)`: Rows for one region

## How it works

`=FILTER(array, include, [if_empty])`. The include argument is a TRUE/FALSE test the same height as the array, such as `C2:C11=G2`.

Combine tests by multiplying them for AND, `(C2:C11=G2)*(D2:D11>G3)`, or adding them for OR. Feed the result to another function: `SUM(FILTER(…))`.

## Syntax

| Formula | What it does |
|---|---|
| `=FILTER(A2:D11,C2:C11=G2)` | Rows for one region |
| `=FILTER(A2:D11,(C2:C11=G2)*(D2:D11>G3),"None")` | Two conditions, with a fallback |
| `=SUM(FILTER(D2:D11,C2:C11=G2))` | Total of the filtered rows |

## Watch out for

- If nothing matches, FILTER returns #CALC! unless you give it the third argument.
- Wrap each test in its own parentheses before multiplying.

## The exercises

Solve them in the live grid at [FILTER](https://spreadsheetpractice.com/learn/filter-function). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Filter by region.** In F5, return every row of A2:D11 where the region matches G2.
2. **Two conditions.** In F5, return rows where the region matches G2 and the amount is greater than G3. If nothing matches, show None.
3. **Total the filter.** In G12, total the amounts for the region in G2 using SUM around FILTER.

Previous: [UNIQUE & SORT](https://spreadsheetpractice.com/learn/unique-sort) · Next: [LET](https://spreadsheetpractice.com/learn/let-function)

---

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