Using it, you can easily view the desired part of a large dataset without searching for it manually. Also, it allows you to enter multiple conditions, which further helps in narrowing down the data.In this article, we explain how you can create a filter function and how it works in detail.
How to Use the Filter Function on Google Sheets?
Before you move on to different ways to use the filter function, we recommend you understand its syntax.Syntax:=FILTER(range, condition1, [condition2,…])The filter function accepts two types of arguments; range and condition.
Range: Range of values in a column block you want to view after filtering. (can contain one or more columns)Condition 1: Row or column that returns an array of boolean values (True or False)Condition 2: Optional conditions you can add to narrow down the filter results.
Using Filter Function with a Single Condition
Here, we input only one condition along with the range argument inside the filter function.Syntax:=FILTER(range, condition)To get a clear picture, let’s have a look at an example.Here, we want to use the filter function to get only the names of people whose salary is greater than 2000.
Using the Filter Function with Multiple Conditions
Sometimes a single condition isn’t enough, and you may need to have multiple conditions to extract the required values. And, since you have multiple conditions, you also have multiple cases.
Using AND Condition
The AND condition returns only the values that match all the conditions. While specifying them on the filter function, you separate them with a comma sign to use the AND condition. Let’s use AND in our example to get a better picture.First, let’s filter the names of people with the “Manager” role and whose salary is greater than 1000.Here, we have two people with the manager role (Jack and Jello), and all people except Jack have salary greater than 1000. So, the only value matching both conditions is Jello.
Using OR Condition
The OR condition returns values that match at least one of the conditions. While specifying multiple conditions on the filter function, you separate them with a plus sign to use the OR condition.Now, if we want to filter names with either the manager role or salary greater than 1000, we use the OR operator.We can just repeat the process similar to the steps above in the AND section. However, instead of the comma sign, we use the plus sign and enclose each condition with parentheses.The filter function now becomes:=FILTER(A5:A9,(B5:B9=“Manager”)+(C5:C9>1000))Here, the plus sign represents the OR operator.The result after applying the OR operator is as follows.Here, all the people have a salary greater than 1000 except Jack. However, Jack satisfies the Manager role condition. Therefore, all the names are displayed in the final result.
How to Use the Filter Function with Other Functions?
You can even use the filter function in combination with other functions, such as the SORT function or any other.Let’s look at an example where we sort the values after performing a filter function.Even though SORT appears first while writing the formula, it’s calculated only after performing the FILTER function. This is because the innermost function gets solved first and is used as an argument for the next outward function.On the other hand, you can even use one filter function as an input (argument) for the other filter function.
Related Questions
How to Use Filter Text that Contains a Specific Letter or Word?
You can enclose the word or letter inside the double quotes to search for texts that contain a specific word. However, the text is case-sensitive, which means “Apple” and “apple” aren’t the same.On the other hand, you can create a custom filter function to get values that do not contain a particular text but is a little complex. Therefore, you need to use an additional function called “REGEXMATCH.”For example, if you want to exclude values that don’t contain the last name “Smith.” So, the formula becomes =FILTER(A5:A9, REGEXMATCH(A5:A9, “Smith”)=FALSE)
