The FILTER formula in Google Sheets helps us filter and return the rows in a range that meet
FILTER(range, condition1, [condition2, …])
- range – is the address reference to the range of cells that the formula filters.
- condition1 – is an array, row or column equal in length or width as that of the corresponding first row or column of range respectively. It contains evaluated TRUE or FALSE values.
- condition2 … – these are optional and additional arrays, rows or columns containing evaluated TRUE or FALSE values to specify if the corresponding row or column within the range needs consideration for the filtering process.
Please note that we cannot use both row and column conditions in the same formula. Either all the conditions should be of column type or row type, and the FILTER formula doesn’t allow mixing them.
Usage: FILTER formula Google Sheets
Here is the sample data on which we will try various combinations of the FILTER formula, and understand its behavior.
Case 1: FILTER(range, condition1)
In this example, we will use just one condition and see how it works.
In the above example, our condition to filter on the first column is “Vegetables”. So the FILTER formula fetches all those rows where the first column has the value “Vegetables”.
Please note that we have keyed in the formula in the cell E1, and accordingly the returned data flows from E1 towards the right and further down. The number of columns the formula returned is same as that of the input range. However, the number of rows might vary based on the filter conditions. So, it is very important that we keep the cells, where we expect the data to flow, clear of any values. Otherwise, the formula returns #REF! error. This is demonstrated in the screenshot below.
Case 2: FILTER(range, condition1, condition2)
Let us add one more filter condition and see what happens.
We essentially asked Google Sheets to show those rows that belong to
Case 3: FILTER(range, condition1, condition2, condition3)
We will add one more condition and see the outcome.
And it works like a charm!
Case 4: When there is no match!
What would happen if it doesn’t find any rows that match the filter conditions? Of course, it throws up an error!
Case 5: When we mix things up!
We already know we can’t input row and column type conditions within a single FILTER formula. Let us try that anyway and see what Google Sheets has to say.
And that’s all there is to it! 🙂
The UNIQUE formula in Google Sheets can be of great help to you as well. Check it out in our blog post: How to use the UNIQUE formula in Google Sheets.