Check Sheet Logo
Check Sheet

FILTER

The FILTER formula in Google Sheets is used to filter a range of data based on specified conditions. It returns a new range that only includes the rows that meet the specified conditions. This formula is commonly used to extract specific data from a larger dataset.

Usage

Use the FILTER formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:

=FILTER(range, condition1, [condition2])
Parameters:
  1. range (required):
    The range of data to be filtered.
  2. condition1 (required):
    The first condition to be applied to the range.
  3. condition2 (optional):
    An optional second condition to be applied to the range.

Examples

Here are a few example use cases that explain how to use the FILTER formula in Google Sheets.

Filtering by a single condition

You can use FILTER to extract data from a range that meets a single condition, such as all values greater than a certain number.

Filtering by multiple conditions

You can also use FILTER to extract data from a range that meets multiple conditions, such as all values that fall within a certain range.

Filtering by date range

FILTER can be used to extract all data within a specified date range.

Common Mistakes

FILTER not working? Here are some common mistakes people make when using the FILTER Google Sheets Formula:

Incorrect range format

One common mistake is using an incorrect range format. Make sure the range is correctly formatted, for example A1:B10.

Incorrect syntax

Another common mistake is using an incorrect syntax. Make sure the syntax is correct, for example FILTER(A1:B10, A1>5).

Missing range parameter

A third common mistake is forgetting to include the range parameter. Make sure the formula includes the range parameter, for example FILTER(A1:B10, A1>5).

Incorrect condition format

One mistake is using an incorrect condition format. Make sure the condition is formatted correctly, for example A1>5.

Incorrect number of conditions

Another mistake is using an incorrect number of conditions. Make sure the formula includes at least one condition, and up to two conditions can be included.

The following functions are similar to FILTER or are often used with it in a formula:

  • SORT

    The SORT formula in Google Sheets is used to sort a range of data based on one or more columns. This function can be used to sort data in ascending or descending order, and can also sort by multiple columns. The sorted data can be used for further analysis or presentation.

  • QUERY

    The QUERY function in Google Sheets is used to query data from a specified dataset. The function allows users to filter and sort data based on specific criteria. The function is commonly used in data analysis and reporting, as well as in creating dynamic dashboards.

  • SUMIF

    The SUMIF formula is used to add up values in a range that meet a specific criterion. It can be used to sum values based on text, numbers, or dates. The formula is most commonly used in financial analysis, budgeting, and data analysis.

  • COUNTIF

    The COUNTIF formula counts the number of cells within a specified range that meet a certain criterion. This formula is commonly used to count cells that meet a specific condition or criteria.

Learn More

You can learn more about the FILTER Google Sheets function on Google Support.