Lesson 33: FILTER Function Exercises

FILTER Function

Practice the Google Sheets FILTER function: pull out the rows that match one condition, several conditions, or either of two conditions, with results that update by themselves.

Task 1 of 4

List the reps who work in the West region. Write one formula in G2 and let it spill down.

Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.

Pulling matching rows out with FILTER

FILTER returns every row of a range that passes your conditions. You write it in one cell and the results spill into the cells below and to the right. When the source data changes, the list rebuilds itself, which a manual filter view can't do.

=FILTER(range, condition1, [condition2, ...])

Each condition is a comparison over a column with the same number of rows as range, such as B2:B9="West" or E2:E9>2000.

AND and OR

AND: give FILTER more conditions, separated by commas. A row has to pass all of them.
OR: add the conditions together inside one argument: (B2:B9="East")+(B2:B9="North"). A TRUE counts as 1, so the sum is above zero when either one matches.

Things that go wrong

#N/A means nothing matched. Wrap it in IFERROR(FILTER(...), "None") to show a friendlier message.
#REF! means something is in the way of the spill. Clear the cells below and to the right of the formula.
#VALUE! with "mismatched range sizes" means the range and a condition cover different rows, for example A2:A9 against B2:B10.