# How to use FILTER with multiple criteria in Excel

Multiply the conditions for AND and add them for OR: =FILTER(table,(cond1)*(cond2)).

**Excel:** `=FILTER(A2:C10,(B2:B10=G1)*(C2:C10>=G2))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Customer | Region | Amount |  |  | Region | West |
| Northwind | West | 1840 |  |  | At least | 1000 |
| Contoso | East | 420 |  |  |  |  |
| Fabrikam | West | 395 |  |  |  |  |
| Litware | North | 1720 |  |  |  |  |
| Adatum | West | 2210 |  |  |  |  |
| Proseware | East | 1955 |  |  |  |  |
| Wingtip | West | 610 |  |  |  |  |
| Tailspin | South | 380 |  |  |  |  |
| Lucerne | West | 1100 |  |  |  |  |

Formula in G4: `=FILTER(A2:C10,(B2:B10=G1)*(C2:C10>=G2))`

Result (spills 3 × 3): Northwind, West, $1,840; Adatum, West, $2,210; Lucerne, West, $1,100

## How it works

Each condition is a column of TRUE and FALSE. Multiplying them gives 1 only where both are TRUE, which is AND. Adding them, `(B2:B10="West")+(B2:B10="East")`, gives a non-zero number where either is TRUE, which is OR.

## Other ways

- `=FILTER(A2:C10,(B2:B10=G1)+(C2:C10>=G2))`: With + it keeps rows that meet either condition (OR). (Rows that match the region or the amount.)

## Common mistakes

- Put each condition in its own brackets. `B2:B10=G1*C2:C10>=G2` multiplies before comparing and gives nonsense.
- AND() and OR() don't work inside FILTER: they return one TRUE or FALSE for the whole range, not one per row.

## Questions

**How do I filter by a list of values?**

Use COUNTIF as the condition: `=FILTER(A2:C10,COUNTIF(J2:J4,B2:B10))` keeps rows whose region is in J2:J4.

**How do I filter rows that contain text?**

Use SEARCH: `=FILTER(A2:C10,ISNUMBER(SEARCH("ware",A2:A10)))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-29.

Source: https://spreadsheetpractice.com/formulas/filter-with-multiple-criteria