Excel 365 + Google Sheets · FILTER (2 conditions)
FILTER with two conditions
Multiply the conditions with * to keep only rows where both are true.
The formula
=FILTER(A2:C6, (B2:B6="North") * (C2:C6>1000))What each part means
A2:C6What to return(B2:B6="North")1st condition* (C2:C6>1000)AND 2nd condition
Example
| A | B | C | |
|---|---|---|---|
| 1 | Client | Region | Amount |
| 2 | Acme | North | 1,200 |
| 3 | Bolt | South | 1,900 |
| 4 | Cora | North | 700 |
| 5 | Dyno | North | 1,450 |
| 6 | Echo | South | 300 |
| 7 | |||
| 8 |
| A | B | C | |
|---|---|---|---|
| 1 | Client | Region | Amount |
| 2 | Acme | North | 1,200 |
| 3 | Bolt | South | 1,900 |
| 4 | Cora | North | 700 |
| 5 | Dyno | North | 1,450 |
| 6 | Echo | South | 300 |
| 7 | Acme | North | 1,200 |
| 8 | Dyno | North | 1,450 |
Step by step
- Click cell A7.
- Type the formula:
=FILTER(A2:C6, (B2:B6="North") * (C2:C6>1000)) - Press Enter.
- The results fill the cells below by themselves (it’s a dynamic array formula). Don’t type anything in them.
Where it works
- Excel 365 and Excel 2021 or newer
- Google Sheets
- Excel for the web (free)
The same formula in other languages
- English FILTER (2 conditions)
- Español FILTRAR (2 condiciones)
- Português FILTRO (2 condições)