Only in Sheets

FILTER function in Google Sheets

FILTER returns the rows of a range that meet one or more conditions.

FILTER shows the rows of a table that pass a test, in another place, and keeps the list up to date as the data changes. It is the function to reach for when someone asks, "can I see just the rows for this customer?"

Syntax

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

Examples

• =FILTER(B2:F13,C2:C13="East")Copy
  Result:

  Asha	East	Pen	30	300
  Meena	East	Notebook	40	2400
  Ravi	East	Notebook	25	1500

  Every row where the region is East.

• =FILTER(B2:F13,C2:C13="North",D2:D13="Bag")Copy
  Result:

  Meena	North	Bag	4	3000
  Ravi	North	Bag	2	1500
  Asha	North	Bag	3	2250

  Two conditions, separated by a comma, mean both must be true.

• =FILTER(B2:B13,F2:F13>2000)Copy
  Result:

  Meena

  Meena

  Asha

  Asha

  One column back: who made a sale above 2000.

• =FILTER(D2:F13,(B2:B13="Kabir")+(B2:B13="Ravi"))Copy
  Result:

  Pen	50	500
  Notebook	15	900
  Bag	2	1500
  Pen	60	600
  Notebook	25	1500

  Adding two tests means either may be true: sales by Kabir or by Ravi.

• =FILTER(B2:F13,C2:C13="West")Copy
  Result: #N/A

  No row matches. Sheets returns #N/A with the note that no matches were found.

• =IFERROR(FILTER(B2:F13,C2:C13="West"),"No sales")Copy
  Result: No sales

  IFERROR supplies a message for the empty case.

• =SUM(FILTER(F2:F13,C2:C13="South"))Copy
  Result: 5750

  FILTER inside SUM: the total for the South.

How it works

The first argument is the data. Each argument after it is a condition: a comparison on a column that has the same number of rows as the data. FILTER returns the rows for which every condition is true.

The result spills into the cells below and beside the formula. Those cells must be empty.

Conditions

• All must be true (and): list the conditions with commas, as in the second example.
• Any may be true (or): add the conditions inside brackets, (test1)+(test2), as in the fourth.
• Contains a text: =FILTER(A2:F13, REGEXMATCH(D2:D13, "book")), or ISNUMBER(SEARCH("book", D2:D13)).
• Not empty: =FILTER(A2:A, A2:A<>""), a handy way to drop blank rows.
• A value from a cell: C2:C13=H1. Change H1 and the list changes.

Dates compare like numbers: A2:A13>=DATE(2026,3,1).

When nothing matches

FILTER returns the error #N/A when no row passes. Unlike Excel's version, it has no argument for a message, so wrap it in IFERROR, as the sixth example does.

Filtering columns

FILTER can also pick columns, when the condition is a single row as wide as the data: =FILTER(A1:F13, A1:F1<>"Units") returns every column except Units. A list of ones and zeros in curly brackets does the same by position: =FILTER(A2:F13, {1,0,0,1,0,1}).

With other functions

• =SORT(FILTER(...), 5, FALSE) filters and then sorts.
• =UNIQUE(FILTER(...)) removes repeats from the matches.
• =COUNTA(FILTER(...)) or =ROWS(FILTER(...)) counts them.
• =SUM(FILTER(...)) and =AVERAGE(FILTER(...)) total them.
• =JOIN(", ", FILTER(...)) puts the matches in one cell.

FILTER or QUERY?

Both pick rows. FILTER is simpler and its conditions are ordinary formulas, so anything a formula can test, FILTER can use. QUERY is better when you also want to choose columns, sort, and work out totals in the same step.

FILTER the function and filter the menu

Data, Create a filter adds buttons to the heading row and hides the rows you untick, in place. The FILTER function leaves the data alone and builds a separate, live list. A filter view lets each person filter without disturbing anyone else. Use the menu to explore, and the function to build a report.

Common mistakes

• Mismatched sizes. The condition must cover exactly the same rows as the range. B2:F13 with C2:C12 gives an error saying the ranges differ in size.
• Typing into the cells where the result must spill, which gives #REF!.
• Including the heading row in the range, so that it is tested like data. Type the headings above the formula.
• Using AND or OR. They squash the whole column into one answer. Use commas or +.

In Excel

Excel 2021 and Microsoft 365 have a FILTER with a different shape: =FILTER(array, include, [if_empty]). It takes one condition argument, so several conditions are multiplied together, (test1)*(test2), and it has a third argument for the "nothing found" message. Formulas need rewriting when a file moves between the two.

Related functions

• QUERYOnly in Sheets
• SORT and SORTNOnly in Sheets
• UNIQUE and COUNTUNIQUEOnly in Sheets
• ARRAYFORMULAOnly in Sheets
• IFERROR and IFNALogical

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.