FILTER

• How to use FILTER formula?
• Examples of using FILTER formula
• FILTER formula not working?
• Similar formulas to FILTER

Usage

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

=FILTER(range, condition1, [condition2])

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

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

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.

Related Formulas

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.