This guide covers everything you need to know about the Google Sheets FILTER function, including its definition, syntax, use cases, and how to use it.
What is the FILTER Function? How Does It Work?
The FILTER function in Google Sheets returns a filtered version of a source range, producing only the rows or columns that meet specific conditions. For example, you can use the FILTER function to extract only the rows from a list of sales records where the sales amount is greater than a certain value.
The FILTER function can be extremely useful in a variety of scenarios, particularly when dealing with large datasets. Whether you’re looking to identify specific data trends, filter out irrelevant data, or simply organize your data in a more manageable way, the FILTER function can greatly enhance your data analysis capabilities.
It’s important to note that the FILTER function only works with rows or columns at any time. If you need to filter both rows and columns, you would need to use the output of one FILTER function as the range in another.
Additionally, if the FILTER function finds no values that satisfy the given conditions, it will return an #N/A error.
FILTER Syntax
The syntax and arguments for the function are as follows:
FILTER(range, condition1, [condition2, …])
Here’s what each argument represents:
- range: This is the data that you want to filter. It represents the source range of cells that you want to apply the filter to.
- condition1: This argument is a column or row containing true or false values corresponding to the first column or row of the range, or it could be an array formula that evaluates TRUE or FALSE. This condition determines which rows or columns from the range will pass through the filter.
- condition2, …: These are optional additional conditions. They should be rows or columns containing boolean values (TRUE or FALSE) that indicate whether the corresponding row or column in the range should pass through the filter. These conditions can also contain array formula expressions that evaluate to such rows or columns. All conditions must be of the same type (either row or column). Mixing row conditions and column conditions is not allowed.
Usage notes related to syntax and arguments:
- The condition arguments must have the same length as the range. If your range includes 10 rows, your condition must also include 10 values (either TRUE or FALSE).
- The FILTER function can only be used to filter rows or columns at one time, not both simultaneously. If you want to filter both rows and columns, you would need to use the return value of one FILTER function as the range in another FILTER function.
- If the FILTER function finds no values that satisfy the provided conditions, it will return #N/A. This means that none of the data in the specified range met the conditions you set.
Examples of How to Use the FILTER Function
Here are some practical examples of how to use the FILTER function in Google Sheets:
Example #1: Filtering a List Based on a Single Condition
Let’s say you have a data set of employees with their respective salaries and want to filter out those who earn more than $5,000.
Assume that column A contains the names of the employees and column B contains their respective salaries. The FILTER function you would use is:
=FILTER(A2:B10, B2:B10>5000)
This function will return a list of employees earning over $5,000.
Example #2: Filtering Based on Multiple Conditions
Now, let’s say you want to filter out employees who earn over $5,000 and are in the Marketing department. Assume that column C contains the department information. The FILTER function you would use is:
=FILTER(A2:C10, B2:B10>5000, C2:C10=”Marketing”)
This function will return a list of employees in the Marketing department who earn more than $5,000.
Example #3: Filtering Non-Blank Cells
If you want to filter out non-blank cells in a list, use the FILTER function with the NOT and ISBLANK functions. Let’s say column A contains a list of items, and some cells are blank. The FILTER function you would use is:
=FILTER(A2:A10, NOT(ISBLANK(A2:A10)))
This function will return a list of items that are not blank.
Example #4: Filtering Based on Dates
Let’s say you have a list of tasks with their respective due dates and you want to filter out those due after a certain date. Assume that column A contains the tasks and column B contains the due dates. The FILTER function you would use is:
=FILTER(A2:B10, B2:B10>DATE(2021,1,1))
This function will return a list of tasks that are due after January 1, 2021.
Example #5: Filtering Based on Text Conditions
If you want to filter out cells that contain a certain text, you can use the FILTER function with the SEARCH function. Let’s say column A contains a list of items, and you want to filter out those that contain the word “apple”. The FILTER function you would use is:
=FILTER(A2:A10, ISNUMBER(SEARCH(“apple”, A2:A10)))
This function will return a list of items that contain the word “apple”.
FILTER: Common Mistakes & Problems
When using the FILTER function in Google Sheets, there are several common mistakes and problems that users often encounter:
- Incorrectly defining the range: The FILTER function requires a specific range to work on. The function might return an error if you don’t specify the range correctly.
- Using the wrong condition: The FILTER function uses a condition to filter the specified range. If the condition is not correctly formatted or is illogical, the function will not work as expected. Ensure the condition is correctly formatted and makes logical sense for the data you filter.
- Ignoring data types: The FILTER function in Google Sheets is case-sensitive and sensitive to data types. If you’re filtering text, ensure the case matches exactly. If you filter numbers, ensure you’re not accidentally treating them as text.
- Not handling #N/A errors: If the FILTER function can’t find any rows that meet your criteria, it will return an #N/A error. You need to build in error handling to deal with this possibility. You can use the IFERROR function to return a specific value or message when FILTER results in an #N/A error.
- Using non-Boolean expressions: The FILTER function requires a Boolean (TRUE or FALSE) expression to determine which rows to include. The function will not work correctly if your condition does not return a Boolean value.
- Excluding empty cells: If your range includes empty cells, the FILTER function might return unexpected results. Remember to account for this in your condition or exclude empty cells from your range.
- Mixing ranges with different sizes: The FILTER function requires that the array and include arrays have the same size. If they don’t, you’ll get a #VALUE! error. Ensure the ranges you use in your FILTER function have the same number of rows and columns.
- Not using ARRAYFORMULA with FILTER: In some cases, you might need to use the ARRAYFORMULA function with FILTER, especially if you want to filter based on conditions in multiple columns to avoid getting errors or incorrect results.
Why Is FILTER Not Working? Troubleshooting Common Errors
If you’re struggling with the FILTER function in Google Sheets, you’re not alone. It’s common to encounter errors while using this feature, but don’t worry – most problems can be resolved with a bit of troubleshooting. In this section, we’ll explore some of the most common errors, their causes, and how to fix them.
#VALUE! Error
Cause: The #VALUE! error typically appears when the provided range does not meet the criteria set in the FILTER function. This might happen if your function’s range and condition arrays do not have the same dimensions.
Solution: To fix this, make sure the range and condition arrays in your function match in size. If you’re using multiple conditions, ensure each array has the same number of rows and columns.
#N/A Error
Cause: The #N/A error usually appears when the FILTER function cannot find any values that meet the set criteria. This means that no rows in your range match the conditions specified.
Solution: To solve this, double-check your criteria. Make sure the conditions you’ve set are accurate and that there are indeed matching values in your range. If you want to avoid the #N/A error even when there are no matches, use the IFERROR function to return a specific value instead.
#REF! Error
Cause: The #REF! error is commonly caused by the array result being too large. This means that the FILTER function is trying to return more rows or columns than available in the sheet.
Solution: To fix this, ensure enough space in your sheet for the FILTER function’s result. You might need to delete some data or move your function to a new location with more room for the output.
#NAME? Error
Cause: The #NAME? error usually occurs when Google Sheets does not recognize the function name. This could be due to a typo or a non-existent function name.
Solution: To resolve this, spell the function name correctly. In this case, it should be FILTER.
Circular Dependency Detected Error
Cause: This error is triggered when the range you’re filtering includes the cell where you’re inputting the formula. This creates a circular dependency, where the result depends on its own value.
Solution: To avoid this, ensure that the cell in which you’re entering the formula is not included in the range you’re applying the FILTER function to.
Using FILTER With Other Google Sheets Functions
Integrating FILTER with other functions allows you to create more specific, complex, and dynamic filters to manipulate and analyze your data. Below are some examples that show how to use FILTER with other Google Sheets functions.
With COUNT
Usage: The COUNT function can be used with FILTER to count the number of rows that meet certain criteria.
Example: Suppose you have a list of employees with their respective departments and salaries. You can use the FILTER function combined with the COUNT function to find out how many employees in a specific department have a salary above a certain amount.
=COUNT(FILTER(B2:B10, C2:C10="Sales", D2:D10>50000))
In this example, B2:B10 represents the range containing the employee names, C2:C10 contains the departments, and D2:D10 contains the salaries. The formula counts the number of sales employees with a salary greater than 50,000.
With SUM
Usage: The SUM function can be used with FILTER to sum the values of rows that meet certain criteria.
Example: Using the same list of employees, you can use the FILTER function combined with the SUM function to find out the total salary of employees in a specific department.
=SUM(FILTER(D2:D10, C2:C10="Sales"))
In this example, D2:D10 represents the salary range while C2:C10 contains the departments. The formula sums the salaries of all sales employees.
With AVERAGE
Usage: The AVERAGE function can be used with FILTER to calculate the average values in rows meeting certain criteria.
Example: You can use the FILTER function combined with the AVERAGE function to determine the average salary of employees in a specific department.
=AVERAGE(FILTER(D2:D10, C2:C10="Sales"))
In this example, D2:D10 represents the range containing the salaries and C2:C10 contains the departments. The formula calculates the average salary of all sales employees.
For more details on the FILTER function, check out the official documentation at the Google Docs Editors Help Center.
