FILTER Function

The FILTER function returns the array after filtering data in a range that satisfies multiple conditions.
The range must not be a table.
This function does not make any changes to the orginal data.
The results are input dynamic so the data changes the results are automatically updated.
The results will automatically spill over into adjacent cells.

Filter with 1 Condition (equal)

Show all the rows in the cell range "B3:D14" where "Country" = "United States".
The Country data is in cells "B3:B14".

Filter with 1 Condition (equal, case sensitive)

Show all the rows in the cell range "B3:D14" where "Cities" = "NEW YORK".
The FILTER function is not case sensitive by default.
You can use the EXACT function (which is case sensitive) to perform a case sensitive text match.

Filter with 1 Condition (less than)

Show all the rows where "Sales" < 50.
The Sales data is in cells "C3:C14".

Filter with 1 Condition (date comparison)

Show all the rows where "Dates" >= "1 Jul 2022".
You can use the DATEVALUE function to return the date serial number given a date in text format.

Filter with 1 Condition (no matches)

Show all the rows where "Country" = "UK".
You can provide a third argument (text string) to be displayed when there are no matches.

If you want the result to be blank when there are no matches, supply an empty string ("") as the third argument.
If there are no matches and no third argument is provided, then #CALC! is returned.

Filter with 1 Condition (not enough space)

When you enter this function the spill range will be indicated with a dotted line.
If any of the cells in the spill range are not blank, then #SPILL! will be returned.
The Error Checking smart tag will also be displayed.

Filter with 1 Condition (plus sorting)

Show all the rows where "Sales" < 70.
And then sort the results by Sales (which is column 2) in Descending Order (which is -1).
You can use the SORT function to sort the data before it is displayed.

Filter with 1 Condition (not all columns)

Show all the rows where "Country" = "United States", but only show the first two columns.
You can display some of the columns by putting the FILTER function inside another FILTER function.
The second argument of the outside FILTER function is used to indicate which columns you want to include.
There are 3 columns in total and we only want to include the first 2 columns, so we use {1,1,0}.

Filter with 2 Conditions (AND)

Show all the rows where "Country" = "United States" AND "Sales" < 50.
You can combine more than one condition using a join operator.
If you want both of the conditions use the asterisk (*) for multiplication.

Filter with 2 Conditions (OR)

Show all the rows where "Country" = "United States" OR "Sales" < 50.
You can combine more than one condition using a join operator.
If you want either of the conditions use the plus (+) for addition.

Filter between 2 dates

Show all the rows where "Dates" >= "1 July 2022" AND "Dates" <= "1 September 2022".
You can use the DATE function to return a date given a year, month, day.

Filter to show duplicates

Show all the rows where there is a duplicate in the "Country" column.
You can use the COUNTIFS function to return the number of cells that satisfy multiple conditions.
Both the arguments in the COUNTIFS function are the same.

Filter to remove blanks

Show all the rows that do not contain any blank cells in the range "B3:D14".
This uses the asterisk/multiplication join operator to combine the three separate conditions.

Filter to show blanks

Show all the rows that contain at least one blank cell in the range "B3:D14".
You can use the SUBSTITUTE function to replace one or more characters.
This replaces any empty cells with a single apostrophe.
If you do not use the SUBSTITUTE function the blank cells will be displayed with a zero (0).

Filter with a search (case sensitive)

Show all the rows that contain the characters "an" in the Country column.
You can use the ISNUMBER function to return the value True or False depending if the value is a number.
You can use the FIND function to return the starting position of a substring within a larger text string.
The FIND function is case sensitive by default.

Filter with a search (not case sensitive)

Show all the rows that contain the characters "AN" in the County column.
You can use the ISNUMBER function to return the value True or False depending if the value is a number.
You can use the SEARCH function to return the starting position of a substring within a larger text string.
The SEARCH function is not case sensitive by default.

Filter showing a limited number of rows

Only show the first 2 rows when a FILTER returns more than 2 rows.
You can use the SORT function to sort the data before it is displayed.
You can use the IFERROR function to include a text string to display if there are no matches.
You can use the INDEX function to return the value from a cell range which is the intersection of a row AND a column.
The "row_num" argument uses an array to indicate how many rows we want returned. We only want 2 rows {1,2}.
The "column_num" argument uses an array to indicate how many columns we want returned. We want all 3 columns {1,2,3}.