Looking for the filter button or filter views rather than a formula? See how to filter Google Sheets without affecting other users<\/a>.<\/p>\n If you do not find the information you are looking for in this post, you can check out 100+ Functions & Formulas for Google Sheets & Excel Basics<\/a>.<\/p>\n Working in Excel instead? The Excel FILTER function<\/a> follows the same logic and adds an optional argument for what to show when nothing matches.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This is the basic structure of the filter function:<\/span><\/strong><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=FILTER(range, condition1, [condition2, \u2026])[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Please note that you can\u2019t use both row and column conditions in the same function. All conditions should be either columns or rows, and the FILTER function doesn\u2019t let you mix them.<\/p>\n [\/et_pb_text][et_pb_text module_id=”how-to-use-the-FILTER-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Below is the sample data on which I will try various combinations of the FILTER function to demonstrate its behaviour.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Filter-image-1-1.png” alt=”Google Sheets FILTER function \u2014 sample food data with Category, Food, Energy (Kcal), and Fat (g) in A1:D10″ title_text=”Sample food data with Category, Food, Energy (Kcal), and Fat (g) in A1:D10″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”case-1″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In this first Google Sheets FILTER example, I will use just one condition and see how it works. This is the formula I will use:<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =FILTER(A2:D10,A2:A10=\u201dVegetables\u201d)<\/b><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” custom_padding=”||0px|||” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The condition to filter on the first column is \u201cVegetables\u201d. So the FILTER function fetches all those rows where the first column has the value \u201cVegetables\u201d.<\/p>\n [\/et_pb_text][et_pb_text module_id=”case-1″ _builder_version=”4.20.2″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Filter-image-2-1.png” alt=”Google Sheets FILTER function \u2014 the four Vegetables rows returned from F1 by a one-condition filter” title_text=”The four Vegetables rows returned from F1 by a one-condition filter” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n I placed the function in cell F1, and accordingly the returned data flows from F1 towards the right and further down. The number of columns the function matches that of the input range. However, the number of rows might vary based on the filter conditions.<\/p>\n So, it is very important to keep cells free of data where you expect the data to flow. Otherwise, the function returns a #REF! Error as demonstrated in the screenshot below.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Filter-image-3-1.png” alt=”Google Sheets FILTER function \u2014 #REF! error in F1 because a dummy value in F4 blocks the results” title_text=”#REF! error in F1 because a dummy value in F4 blocks the results” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”case-2″ _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Let\u2019s add one more filter condition and see what happens.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n \u00a0=FILTER(A2:D10,A2:A10=\u201dVegetables\u201d,C2:C10>20)<\/b><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n I essentially asked Google Sheets to show those rows that belong to the \u201cVegetables\u201d category and whose energy is greater than 20 Kcal, and the FILTER function obliged!<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Filter-image-4-1.png” alt=”Google Sheets FILTER function \u2014 Broccoli, Cabbage, and Cauliflower, vegetables above 20 Kcal” title_text=”Broccoli, Cabbage, and Cauliflower, vegetables above 20 Kcal” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”case-3″ _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n I will add one more condition and see the outcome.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =FILTER(A2:D10,A2:A10=\u201dVegetables\u201d,C2:C10>20,D2:D10<0.3<\/b>)<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n And it works like a charm!<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Filter-image-5-1.png” alt=”Google Sheets FILTER function \u2014 Cabbage and Cauliflower meet all three conditions” title_text=”Cabbage and Cauliflower meet all three conditions” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”case-4″ _builder_version=”4.24.1″ custom_padding=”11px|||||” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n So far all the examples have been using \u201cand\u201d conditions \u2013 that is, the data has to meet all the conditions specified in order for it to pass the filter.<\/p>\n However, there is another option \u2013 you can use \u201cor\u201d conditions that will work for data that meets any one of multiple conditions. The example below demonstrates this.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =FILTER(A2:D10,(A2:A10=\u201dVegetables\u201d)+(D2:D10<0.3))<\/b><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As you can see, the filter here is finding any item that is a vegetable OR has less than 0.3g of fat. Because only one condition needs to be met, Apple and Broccoli are both included even though an apple is not a vegetable, and broccoli has more than 0.3g of fat.\u00a0<\/p>\n In order to use an OR condition, all you need to do is put the conditions in brackets and add them together with a plus sign, instead of separating them by a comma.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Filter-image-6-1.png” alt=”Google Sheets FILTER function \u2014 OR condition returns Apple along with the vegetables” title_text=”OR condition returns Apple along with the vegetables” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”case-5″ _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Another helpful way of using the FILTER function is to find the top few items in any category.<\/p>\n In the example below, I\u2019ve filtered the data to find the three foods with the largest amount of calories.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =FILTER(A2:D10,(C2:C10>=LARGE(C2:C10,3)))<\/b><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This is achieved by using the FILTER function in combination with the LARGE function, which identifies the largest numbers in a dataset.<\/p>\n I\u2019ve used the LARGE function to identify the three largest calorie numbers, and then filtered the data according to these. You can use this method to filter for any number of the largest or smallest values.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Filter-image-7-1.png” alt=”Google Sheets FILTER function \u2014 Almonds, Avocado, and Walnuts, the three highest-calorie foods” title_text=”Almonds, Avocado, and Walnuts, the three highest-calorie foods” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”case-6″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the previous example, I found the top 3 foods with the most calories. But as you could see, the results weren\u2019t shown in order from most calorie-dense to least.<\/p>\n To sort the results of the FILTER function, you can combine it with the SORT function.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SORT(FILTER(A2:D10,(C2:C10>=LARGE(C2:C10,3))),3,FALSE)<\/b><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n All I\u2019ve done here is added the SORT function in front of the FILTER function, then told the SORT function to sort based on column 3 in descending order (by indicating False).<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Filter-image-8-1.png” alt=”Google Sheets FILTER function \u2014 SORT lists the top three by energy, Walnuts first” title_text=”SORT lists the top three by energy, Walnuts first” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”case-7″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Of course, not getting any matches generates an error. This is what it looks like.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Filter-image-9-1.png” alt=”Google Sheets FILTER function \u2014 #N\/A error: No matches are found in FILTER evaluation” title_text=”#N\/A error: No matches are found in FILTER evaluation” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”case-8″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You already know you can\u2019t input row and column type conditions within a single FILTER function. I\u2019ll try that anyway and see what Google Sheets has to say.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Filter-image-10-1.png” alt=”Google Sheets FILTER function \u2014 mismatched range sizes error from mixing a row and a column condition” title_text=”Mismatched range sizes error from mixing a row and a column condition” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”how-to-filter-on-multiple-conditions-in” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Cases 2 to 4 above show the two ways to combine conditions in the Sheets FILTER function. Separate the conditions with commas when a row has to meet all of them (AND logic). Put each condition in parentheses and add them together with a plus sign when meeting any one of them is enough (OR logic).<\/p>\n The examples below use a sales table in A2:H1001, with the amount in column F, the sales agent in column G, and the date of the sale in column H.<\/p>\n 1.<\/strong> In an empty cell, type the FILTER function and select the range.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-filter-date-range-select-range.webp” alt=”Google Sheets filter by date range \u2014 FILTER(A2:H1001, typed in J1 with the syntax tooltip” title_text=”FILTER(A2:H1001, typed in J1 with the syntax tooltip” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 2.<\/strong> Add the condition \u2013 column H must be between June 1st and June 30th of 2022 \u2013 and close the parenthesis. Type the ISBETWEEN function and use the date column as the first parameter.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-filter-date-range-isbetween.webp” alt=”Google Sheets filter by date range \u2014 ISBETWEEN on H2:H1001 added with its argument tooltip” title_text=”ISBETWEEN on H2:H1001 added with its argument tooltip” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 3.<\/strong> For the second argument, use the DATE function to provide the start date: 2022, 6, 1. For the third argument, do the same with the end date: 2022, 6, 30.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-filter-date-range-date-function.webp” alt=”Google Sheets filter by date range \u2014 full formula with DATE(2022,6,1) and DATE(2022,6,30)” title_text=”Full formula with DATE(2022,6,1) and DATE(2022,6,30)” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 4.<\/strong> Skip the third and fourth parameters to ensure the filter includes the start and end dates. Close the parentheses and press ‘Enter’ to see the results.<\/p>\n The finished formula is =FILTER(A2:H1001,ISBETWEEN(H2:H1001,DATE(2022,6,1),DATE(2022,6,30))), and it returns every sale dated in June 2022. For more ways to build dates inside a formula, see how to use the DATE function in Google Sheets<\/a>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-filter-date-range-result.webp” alt=”Google Sheets filter by date range \u2014 returned rows dated 6\/1\/2022 onward highlighted in column Q” title_text=”Returned rows dated 6\/1\/2022 onward highlighted in column Q” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 1.<\/strong> Type the FILTER function and select the range.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-filter-multiple-conditions-range.webp” alt=”Google Sheets filter multiple conditions \u2014 FILTER(A2:H1001, typed in J1 with the syntax tooltip” title_text=”FILTER(A2:H1001, typed in J1 with the syntax tooltip” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 2.<\/strong> Add the first condition: values in column G must equal “Agent1”.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-filter-multiple-conditions-first.webp” alt=”Google Sheets filter multiple conditions \u2014 first condition G2:G1001 equals Agent1 added” title_text=”First condition G2:G1001 equals Agent1 added” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 3.<\/strong> Add the second condition: values in column H must be on or after March 15, 2022. Use the DATE function for the date, as in DATE(2022,3,15). If you type 3\/15\/2022 straight into the formula, Sheets reads it as 3 divided by 15 divided by 2022, so every date passes the condition.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-filter-multiple-conditions-second.webp” alt=”Google Sheets filter multiple conditions \u2014 second condition on the date column H2:H1001 added” title_text=”Second condition on the date column H2:H1001 added” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 4.<\/strong> Close the parenthesis and press ‘Enter’ to see the results. The finished formula is =FILTER(A2:H1001,G2:G1001="Agent1",H2:H1001>=DATE(2022,3,15)), and it returns only Agent1’s sales from March 15, 2022 onward.<\/p>\n You can write the same AND filter with an asterisk instead of a comma: =FILTER(A2:H1001,(G2:G1001="Agent1")*(H2:H1001>=DATE(2022,3,15))). Each condition returns TRUE or FALSE, and multiplying them keeps only the rows where both are TRUE.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/google-sheets-filter-multiple-conditions-result.webp” alt=”Google Sheets filter multiple conditions \u2014 Agent1 rows dated 3\/16\/2022 and later highlighted” title_text=”Agent1 rows dated 3\/16\/2022 and later highlighted” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n To keep rows that meet at least one condition, put each condition in parentheses and add them together with a plus sign, as in Case 4. This formula returns every sale made by Agent1 or Agent3:<\/p>\n =FILTER(A2:H1001,(G2:G1001=”Agent1″)+(G2:G1001=”Agent3″))<\/b><\/p>\n Each condition returns 1 for TRUE and 0 for FALSE, so a row passes when the sum is 1 or more. You can mix both kinds of logic in one formula. This one returns sales of $1,000 or more made by either agent:<\/p>\n =FILTER(A2:H1001,((G2:G1001=”Agent1″)+(G2:G1001=”Agent3″))*(F2:F1001>=1000))<\/b><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Let\u2019s go over a short checklist to help you troubleshoot common issues with the filter function:<\/p>\n If this doesn\u2019t work, AI might be the answer. For a great prompt to troubleshoot spreadsheet functions check out 15 awesome Chat GPT prompts for spreadsheet users<\/a>.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n That\u2019s how to get started with the Google Sheets FILTER function. Looking for more spreadsheet tutorials and tips? Check out our other posts below!<\/p>\n The UNIQUE\u00a0function in Google Sheets can be of great help to you as well. Check it out in our blog post: How to use the UNIQUE function in Google Sheets.<\/a><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" The FILTER function in Google Sheets helps you filter and return rows in a range that meet specified criteria. You can also add multiple criteria across columns. The FILTER function generates a new set of data while keeping the original data intact. Looking for the filter button or filter views rather than a formula? See […]<\/p>\n","protected":false},"author":40,"featured_media":42182,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"The FILTER<\/a> formula in Google Sheets helps us filter and return the rows in a range that meet specified criteria. And, we can add multiple criteria across columns. Accordingly, we will be able to generate a new set of data while the original data remains intact.\nWhat is the syntax of the filter function?<\/strong><\/h2>\n
\n
How to use the FILTER function<\/h3>\n
Case 1: FILTER with one condition<\/h3>\n
Case 2: Filter with two conditions<\/h3>\n
Case 3: Google Sheets FILTER, multiple conditions<\/h3>\n
Case 4: Filter with “or” condition<\/h3>\n
Case 5: Largest values<\/h3>\n
Case 6: Sorting filtered results<\/h3>\n
Case 7: What if there is no match?<\/h3>\n
Case 8: Breaking the rules<\/h3>\n
How to filter on multiple conditions in Google Sheets<\/h2>\n
Filter by a date range<\/h3>\n
AND logic: match every condition<\/h3>\n
OR logic: match any condition<\/h3>\n
Google Sheets FILTER not working?<\/h2>\n
\n
You are officially a FILTER builder!<\/h2>\n
Syntax<\/h3>\nFILTER(range, condition1, [condition2, ...])<\/strong><\/span>\n
\n \t
Usage: FILTER Formula<\/h3>\nHere is the sample data on which we will try various combinations of the FILTER formula, and understand its behavior.\n
![\"FILTER](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-42.png\")
<\/h5>\nCase 1: FILTER(range, condition1)<\/h5>\nIn this example, we will use just one condition and see how it works.\n\n
![\"FILTER](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-43.png\")
\n\nIn 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\".\n\nPlease 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.\n\n![\"FILTER](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-44.png\")
\nCase 2: FILTER(range, condition1, condition2)<\/h5>\nLet us add one more filter condition and see what happens.\n\n
![\"FILTER](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-45.png\")
\n\nWe essentially asked Google Sheets to show those rows that belong to Vegetables food category and whose energy is greater than 20 Kcal, and the FILTER formula obliged!\nCase 3: FILTER(range, condition1, condition2, condition3)<\/h5>\nWe will add one more condition and see the outcome.\n\n
![\"FILTER](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-46.png\")
\n\nAnd it works like a charm!\nCase 4: When there is no match!<\/h5>\nWhat would happen if it doesn't find any rows that match the filter conditions? Of course, it throws up an error!\n\n
![\"FILTER](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-47.png\")
\nCase 5: When we mix things up!<\/h5>\nWe 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.\n\n
![\"FILTER](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-48.png\")
\n\n ","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-3869","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"permalink_manager":null,"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/3869","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/comments?post=3869"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/3869\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media\/42182"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media?parent=3869"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/categories?post=3869"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/tags?post=3869"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}