How to Use the FILTER Function in Google Sheets | Multiple Columns
How to Use the FILTER Function in Google Sheets | Multiple Columns
Filter Function - This is a Google Sheet Formula you need to know! What can the Filter function do? What if you’d like to lookup multiple criteria and return ALL match results? You can use Google Sheets FILTER function. You can return filtered results that meet your specified conditions which can be based on AND as well as OR conditions. You can also use the Filter function for multiple columns.
And because FILTER is a function its results are dynamic. In case you change anything in the data, the resulting filtered data will automatically update.
In this video I’m also going to show you how you can get your FILTER results sorted by putting the Filter function inside the Google Sheet Sort Function.
It’s one of my favorite functions and if you are new to Google Sheets, it should be one of the first few functions that you should take a look at. Towards the end you’ll find out the difference between the Filter Function in Google Sheets versus the Filter function in Excel.
Time Stamps 00:00 How to Use the FILTER Function in Google Sheets 04:20 FILTER Function with Multiple Columns 05:21 How to Sort Results in FILTER Function 07:08 Difference between FILTER in Excel vs. Google Sheets
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#googlesheets
Content
0.55 -> In this video,
1.383 -> I'm going to introduce you
to a very powerful function
4.51 -> in Google Sheets, which
is the FILTER function.
8.13 -> With this function,
9.04 -> you get to return multiple match results.
11.59 -> So not just the first
match but all the matches.
15.9 -> And because you're filtering your data
17.91 -> with a function, your results
20.05 -> are going to update automatically.
22.28 -> You can filter based on
one or multiple columns
26.195 -> and I'm going to show you
27.45 -> how you can get your filtered data sorted
30.128 -> in ascending or descending
order in Google Sheets.
34.649 -> (upbeat music)
38.8 -> This video is a part
39.84 -> of my Google Sheets
comprehensive online course.
42.95 -> It has everything you
need to master Sheets.
45.74 -> It takes you from beginner
47.24 -> all the way to Google Sheets expert.
49.71 -> The link to it is in the
description of the video.
52.21 -> Now let's get to the FILTER function.
54.53 -> Here I have some information
56.185 -> on different apps by region, by division
59.53 -> and I'm collecting the sales
and the profit information.
62.66 -> The current dataset goes up to line 58.
66.07 -> What I want to do is get the apps,
68.63 -> sales and profit by region.
72.08 -> So I'm going to add a
dropdown for region here
74.49 -> and the moment I update my selection,
76.554 -> I want to get that list
automatically populated in here.
80.62 -> Because I have multiple
matches for each region,
84.45 -> I'm going to use the FILTER function here.
86.6 -> But first, let's add our dropdown.
89.28 -> Right mouse click.
90.96 -> Go to Data validation.
92.74 -> I want my list from a range.
94.9 -> That range is my region range here
97.96 -> but I'm just going to leave it open ended
100.04 -> so in case I add more data to this
102.65 -> or I have different regions,
104.07 -> it's automatically going
to populate in my dropdown.
107.44 -> I also want to reject the input
109.52 -> so that I accept only values from my list,
113.09 -> from my selection, click and Save.
115.84 -> So let's go and select Europe for now.
119.43 -> Now we're going to start off
with our FILTER function.
122.52 -> One thing you need to know
123.766 -> is that the FILTER function spills.
127.2 -> So we don't have to worry
about the dollar signs
130.16 -> when we do our range references.
132.03 -> We just need to write one formula,
134.12 -> press Enter and we're going
to get all results back.
137.73 -> It's really easy to use.
139.73 -> What does it need.
140.961 -> First requirement is the range.
143.88 -> This is the range that
we want to get back.
146.49 -> So in this case, I want to get
app, sales and profit back.
150.19 -> So I have to select these three columns.
152.36 -> You can select a single column.
154.42 -> More than three columns.
156.23 -> Whatever you want to get back,
158.43 -> that's what you need to select
160.29 -> but you can only select one range.
163 -> So in this case,
164.31 -> I just want these back.
166 -> I'm going to leave this open ended
167.97 -> so in case I add more data,
169.74 -> they will automatically be considered.
172.54 -> Next is the condition.
175.15 -> Take a look at the example here.
177.46 -> The condition is a logical test.
179.66 -> It's checking whether
the values in this range
182.76 -> is greater than five.
184.37 -> Now, here's something important
about the FILTER function.
187.58 -> Ultimately, this function
only includes true values.
192.8 -> So basically, whenever
this value in this range
195.67 -> is greater than five,
197.14 -> it's going to result in a true
198.54 -> and it's going to include that.
199.99 -> If it doesn't, it's going
to result in a false
202.61 -> and it's going to ignore that.
203.907 -> Right, so that's the engine behind this.
206.66 -> Only true values are included.
209.59 -> In this case, my logical
test is for region.
213.68 -> I want to test whether these regions here,
216.89 -> I'm going to leave it open ended as well
219.21 -> whether these are equal to this.
222.61 -> That's the only condition I have.
224.86 -> So one thing you need to make sure of
226.84 -> is that your ranges
are identical in height
231.84 -> because you need to be consistent
233.69 -> between the two datasets.
235.55 -> This all looks good.
237 -> I'm going to close bracket, press Enter
240.09 -> and that's my result.
242.04 -> And I have also added a formula here
244.5 -> so that it's easier for
you to read the formula.
246.9 -> It just uses the FORMULATEXT function.
249.76 -> So now, if I switch my view to Australia,
253.97 -> I get the apps for Australia,
256.556 -> South America, I get
the South American apps.
260.41 -> Now, what if we want
to add more conditions.
262.83 -> So let's say we want
to update this function
265.03 -> to include sales values greater than zero.
269.5 -> So I don't want to show these zeros here,
271.55 -> only include results that
are greater than zero.
274.91 -> This means I have another condition.
277.4 -> And with the FILTER function here,
279.07 -> it's really easy to add more conditions
282.41 -> because check this out.
283.63 -> We get the ability to
add a second condition
286.28 -> or even more conditions.
288.53 -> Our second condition in this case
290.238 -> is that these sales values here,
292.69 -> so from the Sales column,
294.44 -> are greater than zero.
296.69 -> So let's make sure we're consistent.
299.4 -> Now, I'm going to go
with greater than zero.
302.28 -> Now, in case you might
have negative numbers
305.39 -> in a column and you wanted to
do this not equal to a number,
309.68 -> you need to use this sign.
310.97 -> So less than and greater than sign.
313.26 -> In this case, we just
want greater than zero.
316.36 -> That's it, press Enter.
317.76 -> And the zeros are also filtered out.
321.34 -> Now, let's go ahead and source the results
324.27 -> by profit in ascending order.
327.75 -> Okay, so we're going to put
this inside the sort function
332.95 -> because we want to sort this result.
335.81 -> Now, let's go to the end here
337.95 -> and press F1 to bring back the help.
341.07 -> For SORT, after we define our range,
343.27 -> we need to define the sort column.
345.38 -> In this case, what's my sort column?
348.36 -> It's number three, right?
349.38 -> Because profit is the third column.
352.37 -> Then we need to define if
we want it in ascending
354.98 -> or descending order.
356.49 -> I want descending, so I'm
going to go with true,
359.02 -> close bracket, press Enter
361.37 -> and I have my results sorted by profit
364.84 -> in ascending order.
366.27 -> I can switch and everything
updates automatically.
370.92 -> Now, let's just double check
372.41 -> what happens when we add
more information to this.
376.2 -> What if we add information
377.6 -> from a new region?
379.68 -> Let's just copy this part of the dataset.
384.28 -> I'm going to paste it here.
387.07 -> And let's add New Zealand.
389.55 -> So let's say we decide to add New Zealand
393.45 -> as a separate region here.
396.49 -> And let's also add a brand new app
401.17 -> to an existing region.
403.28 -> I'll call this Health Hacks and Enter.
406.17 -> So do these show up automatically in here?
410.3 -> I added a new region, New Zealand.
413.08 -> My information is there.
414.22 -> It's sorted by profit
416.45 -> and I added a new app to Asia,
420.23 -> Health Hacks and it's right here.
422.68 -> Notice that the FILTER function
doesn't have any problems
426.18 -> with duplicate numbers either.
428.53 -> Now, if you're using Excel 365,
430.88 -> you have to be aware that
there is a difference
433.516 -> between the function signature
435.67 -> of the FILTER function for
Google Sheets versus Excel.
440.42 -> The first two requirements are identical
442.25 -> between the applications.
443.83 -> In Excel, the third argument
is the if_empty argument.
448.68 -> Basically what we want returned
450.38 -> if no measures are found
452.46 -> and in Google Sheets, you
can use the third argument
455.637 -> and actually more arguments after that
458.014 -> to add additional criteria.
461.19 -> In Excel, there is a
different way of doing this.
463.86 -> Now, if you're curious,
I have a separate video
465.928 -> on the FILTER function for Excel.
468.35 -> Link to it is below.
470.37 -> That's it for today.
471.62 -> If you're new here and
haven't subscribed yet,
473.973 -> make sure you do that before you leave
477.09 -> and I'm going to see
you in the next video.
