How to use FILTER with multiple criteria in Excel
Formula (Excel and Google Sheets)
=FILTER(A2:C10,(B2:B10=G1)*(C2:C10>=G2))Multiply the conditions for AND and add them for OR: =FILTER(table,(cond1)*(cond2)).
- Result on the example
- a 3-row list that spills from G4 in
G4 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G4. Change any input and the result updates, or edit the formula to experiment.
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.
=FILTER(A2:C10,(B2:B10=G1)*(C2:C10>=G2))Result{3 rows × 3 cols} "Northwind", "West", 1840, "Adatum", …
A2:C109 rows × 3 cols: "Northwind", "West", $1,840, "Contoso", …A range: 9 rows × 3 columns.
B2:B109 rows × 1 col: "West", "East", "West", "North", …A range: 9 cells in one column.
G1"West"The value in G1.
B2:B10=G1{9 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
C2:C109 rows × 1 col: $1,840, $420, $395, $1,720, …A range: 9 cells in one column.
G2$1,000The value in G2.
C2:C10>=G2{9 rows × 1 col} TRUE, FALSE, FALSE, TRUE, …The >= operator tests at least.
B2:B10=G1)*(C2:C10>=G2{9 rows × 1 col} 1, 0, 0, 0, …The * operator multiplies.
FILTER(A2:C10,(B2:B10=G1)*(C2:C10>=G2)){3 rows × 3 cols} "Northwind", "West", 1840, "Adatum", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:C109 rows × 3 cols: "Northwind", "West", $1,840, "Contoso", … - condition1
B2:B10=G1)*(C2:C10>=G2{9 rows × 1 col} 1, 0, 0, 0, …
- range
In Google Sheets
The same formula works in Google Sheets: =FILTER(A2:C10,(B2:B10=G1)*(C2:C10>=G2)). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 3 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =FILTER(A2:C10,(B2:B10=G1)+(C2:C10>=G2)) | spills 7 × 3Different result | 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>=G2multiplies 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Use FILTER with multiple criteria
G4, return the rows for the region in G1 with an amount of at least G2.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))).
Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.