FILTER with Multiple Conditions

Premiumadvanced 15 min4 tasks0/48 pts

FILTER takes any TRUE/FALSE test, so you can combine conditions with arithmetic. Multiply tests for AND: (B2:B10="North")*(C2:C10>700) keeps rows where both are true. Add tests for OR: (B2:B10="East")+(C2:C10>=1500) keeps rows where either is true. Return several columns by giving FILTER a wider array, and wrap it in SORT to order the result.

Syntax

FILTER(array, (test1)*(test2)) · FILTER(array, (test1)+(test2))

Example: =SORT(FILTER(A2:C10,C2:C10>1000),3,-1) lists the big sales, largest first.

Common mistakes

Using AND() inside FILTER: it returns one value for the whole range, not one per row.
Forgetting the brackets around each test.
Leaving data below the formula, which causes #SPILL!.

FILTER Function SORT & SEQUENCE Functions

Topics/Dynamic Arrays and Advanced

FILTER with Multiple Conditions

Premiumadvanced 15 min4 tasks0/48 pts

Syntax

FILTER(array, (test1)*(test2)) · FILTER(array, (test1)+(test2))

Example: =SORT(FILTER(A2:C10,C2:C10>1000),3,-1) lists the big sales, largest first.

Common mistakes

Using AND() inside FILTER: it returns one value for the whole range, not one per row.
Forgetting the brackets around each test.
Leaving data below the formula, which causes #SPILL!.

FILTER Function SORT & SEQUENCE Functions