FILTERThe 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.
- How to use
- Examples of using
FILTERformula not working?
- Similar formulas to
FILTER formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:
- range (required):
The range of data to be filtered.
- condition1 (required):
The first condition to be applied to the range.
- condition2 (optional):
An optional second condition to be applied to the range.
ExamplesHere are a few example use cases that explain how to use the
FILTERformula 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.
FILTERnot working? Here are some common mistakes people make when using the
FILTERGoogle 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.
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:
SORTformula 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.
QUERYfunction 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.
SUMIFformula 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.
COUNTIFformula 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.
You can learn more about the
FILTER Google Sheets function on Google Support.