Here we’re going to look at the FILTER function. But you can add a filter to a table via the filter option in the menu, I hear you cry! You can, but the FILTER function allows you to put those filtered results on a different part of your page or on a different sheet, and thus, not affect the original table. It also allows for more complex filtering, which the filter option in the menu doesn’t offer.
Here are some examples showing you the various uses of the FILTER function and how it can also be combined with other functions, to filter out the information you need, in one single formula.
Example 1 – Filtering a list by a piece of text
Here we have a list of companies and the products they sell. I want to a list of the ones only Widgets Ltd sell.
I want to leave the original list untouched, so I put my FILTER function on a different part of the page, for example, in cell D2. First, I put the same headers as the original list (just by copying and pasting).
Then in cell D2, I write the following FILTER function:
This looks at the range A1:B12 (i.e. the data in the original table), then shows anything that matches the condition. In this case, it’s looking in column A (A1:A12) and shows anything that matches the name “Widgets Ltd”. Note, the use of speech marks for text.
It then automatically lists just those products that Widgets Ltd sells.
Example 2 – Filtering a list by those who still need to pay
An academy has a list of students, the cost of their course and how much they still need to pay. Here I want to make a list of those who still need to pay, so we can contact them.
I put the headers in columns E to G.
Then in cell E2, I write the following FILTER function:
Here I’m looking at the range A2:C12. Note, it’s usually better to not include the header in the range, i.e. not A1.
Then, I want to know who hasn’t paid, which will be anyone who has more than 0 euros to pay in column C. So, I write the range C2:C12 and state anything more than 0.
This then makes a list of those who still need to pay for their course.
Example 3 – Filtering a table by a specific date
Here we have a list of appointments, with the date, time and person’s name.
I want to filter the table to just show those appointments on 24/10.
In cell E2, I write the following FILTER function:
It looks at the table (A2 to C12) and then looks at column A to find dates that match the date in cell H1, i.e. 24/10.
I could also find out the appointments I have today, by just adding the TODAY() function instead of the cell reference:
This then displays just the appointments with today’s date.
Example 4 – Filtering a table between two dates
Here we have the attendance of a particular group of 6 students for the period between 3/10 and 21/10. I want to show the attendance between two dates.
To the side I set up my filtered list, with the same headers as the original list, plus I want to be able to type in the date range (i.e. start and finish date), so I add that in columns P & Q.
In cell I3 I write the following FILTER function:
This looks at the range A3 to G17 (the original list), then filters by two conditions. The first looks in column A (the dates) and shows anything that is equal to or greater than the date set in cell Q1 (i.e. 10/10). The second looks in column A and shows anything that is equal to or less than the date set in cell Q2 (i.e. 16/10). So, effectively it’s looking for dates between 10/10 and 16/10.
It then shows just those rows that meet the criteria.
Example 5 – Filtering a table by a specific month
Here we’ve got a similar situation to the one before, where we have the attendance of a group of students. This time they have attended class across 3 different months, October, November, and December.
I want to filter the information so that it just shows the attendance for November. So, first I set up a place to show the filtered results by copying the first two rows from the original list.
Then in cell I3, I add the following FILTER function:
This looks in the table (A3 to G17) and then looks for a month in column A that equals the 11th month, i.e. November. In case you haven’t come across the MONTH function before, this returns a the month number from a date, so, January is 1 and December is 12, and so on.
As we can see it gets just those rows which are in November:
Example 6 – Counting the number of values that meet the filter condition
This time instead of returning the actual filtered data, we’re going to count how many times the data in the table meets our criteria.
So, using an example, we have similar data to what we saw in Example 2, where we have a list of students, this time with the course month they want, and how much they still need to pay.
I want to find out how many students still need to pay for the November course. So, to the side, I set up a place to enter the month I want (cell F1) and where the result will appear (cell F2).
In cell F2, I write the following FILTER function combining it with the COUNT function:
Let’s first look at the FILTER part. This looks a the table (A2 to C12), then looks to see if two criteria have been meet. Firstly, do the months in column B equal the month in cell F1 (i.e. November)? Secondly, are the figures in column C over 0€? If both criteria are met then it returns a match.
This would normally, display the 2 matches, but I’ve surrounded the FILTER function in a COUNT one, which will just return the number of matches, in this case, two.
The use of this is similar to that of COUNTIF. See my post on this for more info.
Example 7 – Summing up the total of values that meet the filter condition
Using the same data, this time I want to find out how much in total is still to be paid for the November courses. Instead of using the COUNT function I’ll use the SUM one.
I enter the month I want in cell F1 as before.
This time I just want to look in column C, as that’s where the figures I need to add up are. Then it needs to look in column B and find any months which match the month in cell F1, i.e. November. Finally, I surround it all in a SUM function to add up the money that is still to be paid for the November courses, i.e. 0€ + 200€ + 600€ = 800€.
Note, here we don’t need to worry about the values in column C being 0, as adding these up won’t make any difference to the total.
The use of this is similar to that of SUMIF. See my post on this for more info.
Example 8 – Filtering a table using one criterion OR another
In examples 4 and 6, we looked for data that met BOTH criteria. In this final example, we’re going to see how we can look for data that meets one criterion OR another.
Here we have a list of classes and teachers. In column A, we have the level name, column B the different for that level, and the teachers who are teaching those classes.
I want to list just the Junior 1 and 3 classes. So, in columns E, F, and G I set up a place to put the filtered responses.
Then in cell E2, I write the following FILTER function:
This looks at the table (A2 to C18), then looks for data that meets 1 of 2 criteria. Firstly, in column A it’s looking for levels that are called Junior 1 and secondly, in column A, levels that are called Junior 3.
The important thing here is the plus sign between the two criteria. This means that it will display any row that has the level Junior 1 OR Junior 3.
You may have noticed that when the filtered results are returned, only the values are returned and the formatting is not copied. So, sometimes afterwards you will need to add borders, colours, etc to the filtered results.
Also, sometimes you may get an error message instead of returned results. This is sometimes because you’ve not left enough blank space below the filter cell for the results. It won’t overwrite any cells that aren’t empty, it will just return an #REF! error. So make sure you put your FILTER function where there is plenty of space below it.
If you decide to put your filtered results on a different page, you will need to include the sheet name in your formulas. For example, the main table is on Sheet 1, so in the first part of the formula, I write Sheet1!A1:1B12, which refers to that range on Sheet 1.
eBooks now available on Drive, Forms, Sheets, Docs, and Slides:
- “Beginner’s Guide to Google Drive” – iBooks store / Kindle store
- “Beginner’s Guide to Google Forms” – iBooks store / Kindle store
- “Beginner’s Guide to Google Sheets” – iBooks store / Kindle store
- “Beginner’s Guide to Google Docs” – iBooks Store / Kindle store
- “Beginner’s Guide to Google Slides” – iBooks Store / Kindle store