FILTER function
FILTER returns only the rows of a range that pass a test, such as every order from the West region. The result is live, so changing the test cell or the data rewrites it instantly. Combine tests to build AND and OR conditions that SUMIFS-style functions can't handle.
- Available in
- Excel 2021, 2024 and Microsoft 365
- Category
- Dynamic array functions
- Returns
- Only the rows where include is TRUE. It spills.
- Google Sheets
- Google Sheets has FILTER, but extra arguments there are more conditions, not an if_empty value.
Syntax
=FILTER(array, include, [if_empty])
| Argument | Required? | What it is |
|---|---|---|
array | Required | The range to return rows from. |
include | Required | A TRUE/FALSE test with the same number of rows as array, such as B2:B8="West". Multiply tests for AND, add them for OR. |
if_empty | Optional | What to return when no rows pass, instead of #CALC!. |
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 |
|---|---|---|---|
| E2 | =FILTER(A2:C8,B2:B8="West") | Bluebird Cafespills 3 × 3 | Every West order, all three columns |
| I2 | =FILTER(A2:A8,(B2:B8="East")+(C2:C8>2000)) | Northwindspills 4 × 1 | OR logic: East orders or any order over $2,000 |
| J2 | =FILTER(A2:A8,C2:C8>5000,"None over $5,000") | None over $5,000 | Nothing qualifies, so the fallback text shows |
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
| Customer | Region | Amount | West orders | East or over $2,000 | Over $5,000 | ||||
| Bluebird Cafe | West | $1,250 | Bluebird Cafe | West | $1,250 | Northwind | None over $5,000 | ||
| Northwind | East | $480 | Contoso | West | $2,310 | Contoso | |||
| Contoso | West | $2,310 | Tailspin | West | $395 | Adventure Works | |||
| Fabrikam | North | $760 | Proseware | ||||||
| Tailspin | West | $395 | |||||||
| Adventure Works | East | $1,580 | |||||||
| Proseware | South | $2,105 |
Practice FILTER
From lesson 20, FILTER. Graded instantly, then re-checked on changed data.
Filter by region
F5, return every row of A2:D11 where the region matches G2.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #CALC! | No rows passed the test. Add the third argument so the formula says so: =FILTER(A2:C8,B2:B8="West","No matches"). |
| #VALUE! | include is a different height from array. Make both cover the same rows, such as A2:C8 and B2:B8. |
| AND or OR gives one answer for everything | AND() and OR() return a single TRUE or FALSE for the whole range. Multiply the tests for AND and add them for OR. |
| #SPILL! | Something is in the cells the result needs. Clear everything below and to the right of the formula, or move it to an empty area. |
Tips
- Total a filtered list with SUM(FILTER(C2:C8,B2:B8="West")).
- Put the criteria in cells, such as B2:B8=G1, so anyone can change the report without editing the formula.
Questions
How do I filter with multiple criteria?
Multiply the tests for AND, =FILTER(A2:C8,(B2:B8="West")*(C2:C8>1000)), and add them for OR, =FILTER(A2:C8,(B2:B8="West")+(B2:B8="East")).
Can FILTER return only some columns?
Yes. Point array at just the columns you want, such as A2:A8, and keep the test the full height: =FILTER(A2:A8,B2:B8="West").
Why does FILTER return #CALC!?
Nothing matched the test. Add a fallback as the third argument.
Lessons that use FILTER
- Lesson 20: FILTER (Pro)
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.