How to use the FILTER function in Google Sheets (Single or Multiple Conditions)

Key Takeaways

in this video I'm going to show you how to use the filter function in Google sheets now just to be clear I'm not talking about the filter command that's available through the tool bar menu I'm talking about the actual filter formula that you type into the spreadsheet cells so I'm going to show you a variety of ways to use the filter function I'll show you how to filter by number by a cell reference by a text string and also by date and then I'll move on to show you how to filter by more than one condition so let's go ahead and get started with this first example where I'm going to show you how to filter by number so what we have here on the left in columns a and B are lists of student names and their grades and what we want to do is filter this list and only display students who have a grade of 100% so how we're going to do that is type our filter function into cell d3 by typing equals filter parentheses a 3 colon B to capture our source range then type a comma now type our condition which is B 3 colon B equals 1 we're checking column B 4 where it equals 1 or 100% now put your ending parenthesis and press ENTER and you'll see that our filtered results have appeared and it's only displaying students who have a grade of 100% now before we move on to the next example let's talk about some of the operators that we can use when you're using the filter function and by operators I'm talking about things like equals greater than and less than so in this example we're using equals B 3 colon B equals 1 to check column B for where it equals 100% but let's see what happens when we change this equal sign to a less than sign so you'll see that the filtered results have now changed and now we're displaying students with a grade of less than 100 now much the same we can use the greater than sign in the filter function but in this case I'm going to change the number itself because there are no scores greater than 100% and so let's display the scores for the students and their scores of greater than 70% and so now you can see our filtered results have changed again and we're now displaying students who have a grade of more than 70% in this next example I'm going to show you how to filter by a cell value or a cell reference and so we want to achieve a similar task using the same data but we're going to do it in a different way so rather than entering the value that we want to filter by directly into the filter formula we're just going to type that value into a spreadsheet cell and then refer to that cell within the filter function so again we have student names and grades and what we want to do is filter this list and show only students who have agreed of less than 60 percent and we're going to do that by entering our filter function and to cell d3 by typing equals filter parentheses 8 3 : B to capture our source range a 3 through B and comma B 3 : B less than and now we're going to type f1 to refer to the cell f1 where the value that we want to filter by is already entered then we'll type our ending parenthesis and press Enter and now you'll see that the filter results have appeared and we're only showing students that have a grade of less than 60% and we were able to do that without having to type the value that we want to filter by directly into the formula but rather we were able to simply type it into a spreadsheet cell and why we do this is because it makes it really easy for us to change the number or the value that we want to filter by without having to go up into the toolbar and change the formula itself and so now change the value that's entered into cell f1 from zero point six to zero point nine and you can see as soon as I change this value the data that is linked to it and the filter results has also changed and now we're displaying students who have a grade of less than ninety percent in this next example I'm going to show you how to filter by text which is basically the same process as filtering by a number or a cell reference but when you're filtering by text you need to wrap the text in quotation marks or in other words quotation marks need to go on the outside of the text that you want to filter by so in this example we have a list of customers and their payment status and what we want to do is filter this list and only show customers who have a payment status of late and we're going to do that by typing our filter function into cell d3 by typing equals filter parentheses 83 : B comma b3 column B equals quotation mark and then let's type the word late and then put another quotation mark because remember when you're filtering by text you need to wrap it in quotation marks and now we'll put our ending parenthesis and press ENTER and the filter results have appeared and we're now only displaying customers who have a payment status of late so we've already went over a few of the different operators that you can use in the filter function such as equals greater than and less than but in this example I'm going to show you one of the less commonly used but very valuable operators which is not equal to in many cases you want to filter a list of data and exclude results that matches certain criteria and so I'm going to show you how to do that here on the Left we have data related to sales calls that varying sales representatives make and what we want to do is filter this list and show all of the data that is not related to the sales rep Bob and so how we're going to do that is type our filter function into cell G 3 by typing equals filter parentheses a 3 : e to capture our source range a 3 through e then type a comma then B 3 : B and to type not equal to what you'll do is type the less than sign followed by the greater than sign this means not equal to in Google sheets now we'll type of quotation mark because remember when we're filtering by text you want to wrap the text in quotation marks now we'll type the name Bob and put our ending quotation mark and then ending parenthesis and press Enter now we have our filter results and we're showing all of the data that you see on the left except for that which is related to the sales rep Bob so now I'm going to show you how to filter by date and there's actually a couple of different ways that you can do this in Google sheets and so I'll show you both but in this first example of filtering by date what we're going to do is enter the date that we want to filter by and to cell G 1 and then refer to that cell within the filter function just like we learned in the lesson on filtering by a cell value so what we have here is a list of student names they're test grades and the date that their test was taken and we want to filter this list and only display students who took their tests before 6-1 - 2019 so let's go ahead and enter the date that we want to filter by into cell G 1 which again is 6 - 1 - 20 19 then press Enter and now we'll enter our filter function into cell III by typing equals filter parentheses 8 3 : C to capture our source range a 3 through C then type comma C 3 : C less than and now we're gonna type G 1 to refer to the cell G 1 where the date that we want to filter by is already entered and then we'll type our ending parenthesis and press Enter and now we have our filter results which are showing us only students who took their test before 6 - 1 - 20 19 in this next example I'm going to show you another way to filter by date where you don't have to use a cell reference and you can type the date that you want to filter by directly into the filter function and so again we have a list of students their test grades and the date that their test was taken and we want to fill through this list and display only the students who took their test before 6 - 1 - 2019 and in this example we're going to do that by entering our filter function and - cell e3 by typing equals filter parentheses III colon C comma C 3 colon C less than and now type the word date and a parenthesis to begin the date function which is going to allow you to designate the date that you want to filter by directly in the formula here but it goes in a very specific format and so first we need to type the year which is 2019 and type a comma then you'll type the month and another comma and then the date and now you'll need an ending parenthesis for the date function and another ending parenthesis to end the filter function itself then you'll press ENTER and again we have our filter results which are now showing us students who took their test before 6-1 - 2019 so now I'm going to show you how to filter by more than one condition and there's actually two different ways to do this depending on the situation that you're in so I'm going to show you both but first I'm going to show you the most typical way of filtering by two conditions which is where both the conditions that you set must be true for the data to appear in the filter results so what we have here is a list of customers their payment status and their membership status and what we want to do is filter this list and show only the customers who have a payment status of late and who also have a membership status of active so how we're going to do that is enter our filter function into cell III by typing equals filter parentheses a 3 : C to capture the source range then type a comma and now you're going to enter your first condition which is B 3 : B equals the text late now type in comma and you can enter your second condition which is C 3 : see the text active now type an ending parenthesis and press Enter and now we have our filter results which are showing us only customers who have a payment status of late and who also have a membership status of active now I'm going to show you a different way of filtering by two conditions or either of the conditions that you set can be true for the data to appear in the filter results and so we're just going to make a slight change in the formula and it's going to completely change the way that it operates so again we have a list of customers their payment status and their membership status and we want to filter this list and displayed customers who either have a payment status of Li or have a membership status of active now we're going to do that is enter our filter function into cell III by typing equals filter parentheses 8 3 : C to capture the source range and then type comma now we're going to enter our first condition but in this example we want to put parentheses around our conditions and you'll see why in a minute here so let's type a parenthesis and then type B 3 : B equals the text late remember to put your ending parentheses for your first condition and now type a plus sign now we can enter our second condition but remember we want parentheses around it so type of parentheses and then type C 3 colon C the text active remember to put your ending parenthesis for your second condition and then we'll need another parenthesis to end the filter function itself now press ENTER and we have the filter results which are displaying customers who either have a payment status of late or who have an active membership so it's a really small change that's made in the formula which makes a really big difference in the way that it operates where in one case when you separate your filter conditions by a comma both of those conditions must be true for the data to appear in the filter results and in the next case when you separate your filter conditions by a plus sign either of those conditions can be true for the data to appear in the filter results so I hope you like this video if you do please like and subscribe and if you want to copy and paste any of the formulas that you've seen in this video into your own cheat did I've linked the article to this video for you below