If you do not find the information you are looking for in this post, you can check out <\/span>100+ Functions & Formulas for Google Sheets & Excel Basics<\/span><\/a>.<\/span><\/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.<\/span><\/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=”filter-function-google-sheets-1″ title_text=”filter-function-google-sheets-1″ 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<\/span> example, I will use just one condition and see how it works. This is the formula I will use:<\/span><\/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.<\/span><\/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=”filter-function-google-sheets-2″ title_text=”filter-function-google-sheets-2″ 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.<\/span><\/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.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Filter-image-3-1.png” alt=”filter-function-google-sheets-3″ title_text=”filter-function-google-sheets-3″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][\/et_pb_column][\/et_pb_row][et_pb_row admin_label=”row” _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||” theme_builder_area=”post_content”][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<\/span>=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!<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Filter-image-4-1.png” alt=”filter-function-google-sheets-4″ title_text=”filter-function-google-sheets-4″ 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>)<\/span><\/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=”filter-function-google-sheets-5″ title_text=”filter-function-google-sheets-5″ 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.<\/span><\/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.<\/span><\/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<\/span><\/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.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Filter-image-6-1.png” alt=”filter-function-google-sheets-6″ title_text=”filter-function-google-sheets-6″ 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.<\/span><\/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.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Filter-image-7-1.png” alt=”filter-function-google-sheets-7″ title_text=”filter-function-google-sheets-7″ 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.<\/span><\/p>\n To sort the results of the FILTER function, you can combine it with the SORT function.<\/span><\/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).<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Filter-image-8-1.png” alt=”filter-function-google-sheets-8″ title_text=”filter-function-google-sheets-8″ 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.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Filter-image-9-1.png” alt=”filter-function-google-sheets-9″ title_text=”filter-function-google-sheets-9″ 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.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/Filter-image-10-1.png” alt=”filter-function-google-sheets-10″ title_text=”filter-function-google-sheets-10″ 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″ _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:<\/span><\/p>\n If this doesn\u2019t work, AI might be the answer. For a great prompt to troubleshoot spreadsheet functions check out <\/span>15 awesome Chat GPT prompts for spreadsheet users<\/span><\/a>.<\/span><\/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. If you do not find the information you are looking for in this post, […]<\/p>\n","protected":false},"author":40,"featured_media":42182,"comment_status":"open","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<\/span><\/h3>\n
Case 2: Filter with two conditions<\/span><\/h3>\n
Case 3: Google Sheets FILTER, multiple conditions<\/span><\/h3>\n
Case 4: Filter with “or” condition<\/span><\/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
Google Sheets FILTER not working?<\/span><\/h2>\n
\n
You are officially a FILTER builder!<\/span><\/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":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/3869","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/comments?post=3869"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/3869\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media\/42182"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media?parent=3869"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/categories?post=3869"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/tags?post=3869"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}