Spreadsheet Practice

Lesson 20 of 29, Dynamic arrays

FILTER

Return only the rows that pass a test, and keep them live.

Pro10 minutes, 3 exercisesFILTER

Watch it done

3 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

=FILTER(array, include, [if_empty]). The include argument is a TRUE/FALSE test the same height as the array, such as C2:C11=G2.

Combine tests by multiplying them for AND, (C2:C11=G2)*(D2:D11>G3), or adding them for OR. Feed the result to another function: SUM(FILTER(…)).

Syntax

=FILTER(A2:D11,C2:C11=G2)Rows for one region
=FILTER(A2:D11,(C2:C11=G2)*(D2:D11>G3),"None")Two conditions, with a fallback
=SUM(FILTER(D2:D11,C2:C11=G2))Total of the filtered rows

Watch out for

  • If nothing matches, FILTER returns #CALC! unless you give it the third argument.
  • Wrap each test in its own parentheses before multiplying.

The exercises

  1. Filter by region. In F5, return every row of A2:D11 where the region matches G2.
  2. Two conditions. In F5, return rows where the region matches G2 and the amount is greater than G3. If nothing matches, show None.
  3. Total the filter. In G12, total the amounts for the region in G2 using SUM around FILTER.

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.

  1. Filter by region
  2. Two conditions
  3. Total the filter
Try these functions in the Formula Lab