December 3, 2020

FILTER function in Google Sheet

In this lesson we will learn how the FILTER function works in Google Sheet. The FILTER function is used to get the extract from a range of source data filtered by a supplied criteria.

=FILTER (range, criteria)
  • range – The source data range to filter.
  • criteria – There can be one or more than one criteria based on which the filtering occurs.

Let’s understand the behavior of FILTER function with a simple example. Below we have a table containing a list of students in a Motor training school and the miles driven by each one on a serial basis.

Column A contains student names and column B contains the miles driven. 

Here in cell F1 we have entered the below function.

=FILTER(A2:B18,A2:A18=D2)

Here A2:B18 is the data source from here we need to filter based on the criteria A2:A18=D2. Here in cell D2 we have added a student’s name as ‘Peter’. Now it will search for the value ‘Peter’ in range A2:A18 and whenever found, filter the entire row from the range A2:B18 and add those starting from cell F1. Here 2 records have been found for the student ‘Peter’.

Now we will learn using FILTER function with more than one criteria with a simple example.

Let’s say we have the below table containing list of dresses and their price. Now we have added the below formula in cell E2.

=FILTER(A2:C9,(A2:A9="Black")*(B2:B9="Coat")*(C2:C9>35))

Here the range of the source table is A2:A9. In criteria we have added three conditions. We are filtering Black Coat with price more than 35 dollars. The filtered rows have been resulted starting from cell E2.

As you can see below, in source range there are more Coats, some are not black(white), some are black but the price is not more than 35 dollars(30.62). Those are not filtered by our formula as all three criteria are not matching for them. The criteria argument works as Boolean here. If it’s TRUE then only the FILTRATION happens.

Notes :
  • If the FILTER is used between workbooks, both workbooks must be open, otherwise FILTER will return #REF! Error.

Leave a Reply

Your email address will not be published. Required fields are marked *