{"id":1062,"date":"2022-12-02T16:42:03","date_gmt":"2022-12-02T11:12:03","guid":{"rendered":"https:\/\/sheetstips.com\/?p=1062"},"modified":"2022-12-03T17:01:50","modified_gmt":"2022-12-03T11:31:50","slug":"filter-function-google-sheets","status":"publish","type":"post","link":"https:\/\/sheetstips.com\/filter-function-google-sheets\/","title":{"rendered":"How to Use Filter Functions in Google Sheets with Examples"},"content":{"rendered":"<p>Google Sheets comes with various functions such as VLOOKUP, IMPORTRANGE, REGXREPALCE, and so on. The FILTER function is one such amazingly strong one where we can perform various options without using the FILTER Views.<\/p>\n<p>On this page, let&#8217;s learn everything about Filter Functions and operations that can be performed with the help of <strong><a href=\"https:\/\/sheetstips.com\/google-sheets-tips\/\">Google Sheet tips<\/a><\/strong> provided on this page. Read on to find more.<\/p>\n<h2>Syntax for Using Filter Functions in Google Sheets<\/h2>\n<p>The syntax for Filter Functions in Google Sheets are given below:<\/p>\n<p><strong><span style=\"color: #ff0000;\"><span class=\"waffle-arguments-help-function-name\">FILTER<\/span><bdo dir=\"ltr\"><span class=\"waffle-arguments-paren\">(<\/span><span class=\"waffle-arguments-parameter-holder\"><span class=\"waffle-arguments-help-parameter waffle-arguments-help-parameter-active\" dir=\"auto\">range<\/span>,\u00a0<span class=\"waffle-arguments-help-parameter\" dir=\"auto\">condition1<\/span>,\u00a0<span class=\"waffle-arguments-help-parameter\" dir=\"auto\">[condition2, \u2026]<\/span><\/span><span class=\"waffle-arguments-paren\">)<\/span><\/bdo><\/span><\/strong><\/p>\n<ul>\n<li>range \u2013 the filtered data should be within this range.<\/li>\n<li>condition 1: The condition on which the data is filtered is condition 1.<\/li>\n<li>condition2: additional conditions that the data is filtered based on.<\/li>\n<\/ul>\n<p>It&#8217;s important to remember that the FILTER function only returns data that meets all of the specified criteria.<\/p>\n<h3>Highlights of Filter Functions in Google Sheets<\/h3>\n<ul>\n<li>The FILTER function returns an array as a result. This means you won&#8217;t be able to remove a portion of the result.<\/li>\n<li>Filter Functions will produce a #N\/A error if there is no match based on the conditions you gave.<\/li>\n<li>The formula will return a REF error if any cell that is expected to be filled by the result of the FILTER function is already filled.<\/li>\n<\/ul>\n<p>Now that we are aware of the syntax of FILTER functions in Google Sheets. In the below section, let us understand how to use Filter Functions with examples.<\/p>\n<ul>\n<li><a href=\"https:\/\/sheetstips.com\/filter-views-google-sheets\/\">How to Create Filter Views in Google Sheets? (Share\/Delete\/Save\/Duplicate Filter Views)<\/a><\/li>\n<li><a href=\"https:\/\/sheetstips.com\/google-sheets-or-function\/\">How to Use OR Function in Google Sheets With Examples (Logical Functions)<\/a><\/li>\n<li><a href=\"https:\/\/sheetstips.com\/query-function-google-sheets\/\">Google Sheets QUERY Function Explained With Examples<\/a><\/li>\n<\/ul>\n<h3>Filter Functions using Single Condition in Google Sheets<\/h3>\n<p>Let us consider we have an employee dataset and we need to filter only the employee who are belonging to the marketing department. Now follow the steps listed below to achieve the same:<\/p>\n<ul>\n<li><strong>Step 1:<\/strong> Move to the cell where you would like to use the Filter Functions.<\/li>\n<li><strong>Step 2:<\/strong> Now enter the formula using Filter function syntax. In our case, we are using the formula as &#8220;<strong>=FILTER($A$2:$C$17,$B$2:$B$17=&#8221;Marketing&#8221;)<\/strong>&#8220;.<\/li>\n<li><strong>Step 3:<\/strong> Press the <strong>Enter<\/strong> button. Now you will see the data being filtered out without using inbuilt filter options.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-1063 aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets.png\" alt=\"filter-function-google-sheets\" width=\"973\" height=\"438\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets.png 973w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets-300x135.png 300w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets-768x346.png 768w\" sizes=\"(max-width: 973px) 100vw, 973px\" \/><\/p>\n<h3>Filter Functions using Multiple Condition in Google Sheets<\/h3>\n<p>Let us say, in the same dataset, I want to extract the employee data who are working under the marketing department and along with it, I also want to extract whose salary was greater than 50%.<\/p>\n<p>Now follow the steps listed below to achieve the same.<\/p>\n<ul>\n<li><strong>Step 1:<\/strong> Move the cell where you want to extract the data using the filter function.<\/li>\n<li><strong>Step 2:<\/strong> Now use the formula as &#8220;<strong>=FILTER($A$2:$C$17,$B$2:$B$17=&#8221;Marketing&#8221;, $D$2:$D$D17&gt;50%)<\/strong><\/li>\n<li><strong>Step 3:<\/strong> Press the &#8220;<strong>Enter<\/strong>&#8221; button. The results will be as shown in the image below.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1064\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets1.png\" alt=\"filter-function-google-sheets\" width=\"976\" height=\"435\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets1.png 976w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets1-300x134.png 300w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets1-768x342.png 768w\" sizes=\"(max-width: 976px) 100vw, 976px\" \/><\/p>\n<h3>Filter Functions using Odd\/Even Condition in Google Sheets<\/h3>\n<p>It&#8217;s really simple to extract all the even-numbered rows or the odd-numbered rows\u00a0from a dataset using the FILTER function.<\/p>\n<p>Let us consider the same dataset where we need to filter even rows and odd rows using Filter functions. To get this done, follow the steps listed below.<\/p>\n<ul>\n<li><strong>Step 1:<\/strong> Move to the cell where you would like to use the Filter Function.<\/li>\n<li><strong>Step 2:<\/strong> Now use the formula <strong>&#8220;=FILTER($A$2:$D$17, MOD(Row(A2:A17)-1,2)=0)&#8221;<\/strong>.<\/li>\n<li><strong>Step 3:<\/strong> Press the &#8220;<strong>Enter<\/strong>&#8221; button. This will filter all the even-numbered rows as shown below.<\/li>\n<\/ul>\n<p>Since we have represented row 1 as headers, Google Sheets ignores row 1 and starts counting from Row 2.<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1065\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets2.png\" alt=\"filter-function-google-sheets\" width=\"1218\" height=\"435\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets2.png 1218w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets2-300x107.png 300w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets2-1024x366.png 1024w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets2-768x274.png 768w\" sizes=\"(max-width: 1218px) 100vw, 1218px\" \/><\/p>\n<p>To filter the odd number of rows, use the formula <span style=\"color: #ff0000;\"><strong>=FILTER($A$2:$D$17, MOD(Row(A2:A17)-1,2)=1)<\/strong><\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-1066 aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets3.png\" alt=\"filter-function-google-sheets\" width=\"1143\" height=\"442\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets3.png 1143w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets3-300x116.png 300w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets3-1024x396.png 1024w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets3-768x297.png 768w\" sizes=\"(max-width: 1143px) 100vw, 1143px\" \/><\/p>\n<h3>Filter Functions using ABOVE\/BELOW Average in Google Sheets<\/h3>\n<p>You can apply conditions like the data points (or records) should be above or below average when using the FILTER function.<\/p>\n<p>Let us understand how to use above or below Average functions in Google Sheets by following the steps listed below:<\/p>\n<ul>\n<li><strong>Step 1:<\/strong> Move to the cell where you would like to use the AVERAGE Function in Google Sheets.<\/li>\n<li><strong>Step 2:<\/strong> Now enter the formula as &#8220;<strong>=FILTER($A$2:$D$17,$D$2:$D$17&gt;AVERAGE($C$2:$D$17)<\/strong>&#8220;.<\/li>\n<li><strong>Step 3:<\/strong> Press the &#8220;<strong>Enter<\/strong>&#8221; button. You will see the results as shown below.<\/li>\n<\/ul>\n<p>Here we have used the above Average function.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1067\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets4.png\" alt=\"filter-function-google-sheets\" width=\"1131\" height=\"434\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets4.png 1131w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets4-300x115.png 300w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets4-1024x393.png 1024w, https:\/\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-functions-google-sheets4-768x295.png 768w\" sizes=\"(max-width: 1131px) 100vw, 1131px\" \/><\/p>\n<p>The formula to use the below-average function in Google Sheets is <strong>=FILTER($A$2:$D$17,$D$2:$D$17&lt;AVERAGE($C$2:$D$17).<\/strong><\/p>\n<p>Also Read: <a href=\"https:\/\/btechgeeks.com\/python-interview-questions-on-data-types-and-their-in-built-functions\/\" target=\"_blank\" rel=\"noopener\">Python Interview Questions on Data Types and Their in-built Functions<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Google Sheets comes with various functions such as VLOOKUP, IMPORTRANGE, REGXREPALCE, and so on. The FILTER function is one such amazingly strong one where we can perform various options without using the FILTER Views. On this page, let&#8217;s learn everything about Filter Functions and operations that can be performed with the help of Google Sheet &#8230; <a title=\"How to Use Filter Functions in Google Sheets with Examples\" class=\"read-more\" href=\"https:\/\/sheetstips.com\/filter-function-google-sheets\/\" aria-label=\"More on How to Use Filter Functions in Google Sheets with Examples\">Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":2092,"url":"https:\/\/sheetstips.com\/row-and-rows-in-google-sheets\/","url_meta":{"origin":1062,"position":0},"title":"Row and Rows in Google Sheets: Know How Many Rows Are There in Google Sheets","author":"Varsha","date":"May 22, 2022","format":false,"excerpt":"Row and Rows in Google Sheets: The functions ROW and ROWS in Google Sheets are distinct. ROW is a function that returns the row number of a specified cell. Whereas ROWS is a function that returns the number of rows in a specified array range of cells. Both these functions\u2026","rel":"","context":"In &quot;Google Sheets&quot;","block_context":{"text":"Google Sheets","link":"https:\/\/sheetstips.com\/category\/google-sheets\/"},"img":{"alt_text":"row function in google sheets","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/01\/row-number-in-google-sheets2.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1076,"url":"https:\/\/sheetstips.com\/wildcards-google-sheets\/","url_meta":{"origin":1062,"position":1},"title":"How to Use Wildcards in Google Sheets with Examples?","author":"Varsha","date":"May 16, 2022","format":false,"excerpt":"Every Google Spreadsheet user will have to learn how to use the Wildcard characters. Generally, in Google Sheets, we can combine wildcard characters with functions to perform a variety of useful opeartions. In this article, let us explore how to use wildcards with the help of Google Sheet tips provided\u2026","rel":"","context":"In &quot;Google Sheets&quot;","block_context":{"text":"Google Sheets","link":"https:\/\/sheetstips.com\/category\/google-sheets\/"},"img":{"alt_text":"wild cards in google sheets","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2021\/09\/wildcards-google-sheets6.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":803,"url":"https:\/\/sheetstips.com\/filter-views-google-sheets\/","url_meta":{"origin":1062,"position":2},"title":"How to Create Filter Views in Google Sheets? (Share\/Delete\/Save\/Duplicate Filter Views)","author":"Varsha","date":"May 17, 2022","format":false,"excerpt":"Filter Views are one of the most useful features which come in handy when working with Google Sheets. With the help of Filter views, we can easily sort the data which we would like to see and so on. In this article, we will go over the Filter View functions\u2026","rel":"","context":"In &quot;Google Sheets&quot;","block_context":{"text":"Google Sheets","link":"https:\/\/sheetstips.com\/category\/google-sheets\/"},"img":{"alt_text":"filter views in google sheets","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2021\/09\/filter-views-google-sheets5.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":842,"url":"https:\/\/sheetstips.com\/delete-every-other-row-google-sheets\/","url_meta":{"origin":1062,"position":3},"title":"How to Delete Every Other Row in Google Sheets (Using Filter &#038; Formula)","author":"Varsha","date":"May 17, 2022","format":false,"excerpt":"If you want to delete every other row in Google Sheets manually, it is a time-consuming process. In order to save your time, you can use ISEVEN or MOD function, with the help of which you can delete every other row, Nth row, third row, and so on. In this\u2026","rel":"","context":"In &quot;Google Sheets&quot;","block_context":{"text":"Google Sheets","link":"https:\/\/sheetstips.com\/category\/google-sheets\/"},"img":{"alt_text":"delete-every-other-row-google-sheets","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2021\/09\/delete-every-other-row-google-sheets7.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2125,"url":"https:\/\/sheetstips.com\/how-to-find-matching-terms-in-google-sheets\/","url_meta":{"origin":1062,"position":4},"title":"How To Find Matching Terms in Google Sheets? &#8211; Google Sheet Match Multiple Criteria","author":"Varsha","date":"May 22, 2022","format":false,"excerpt":"It's no wonder that Google Sheets allows users to perform various operations using the functions and formulas available on Google Sheets. Similarly, in this article, let us consider an example where we want to construct a single-cell formula that accepts a string of search terms and returns all of the\u2026","rel":"","context":"In &quot;Google Sheets&quot;","block_context":{"text":"Google Sheets","link":"https:\/\/sheetstips.com\/category\/google-sheets\/"},"img":{"alt_text":"matching terms in google sheets1","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/02\/matching-terms-in-google-sheets1.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2041,"url":"https:\/\/sheetstips.com\/subtotal-function-in-google-sheets\/","url_meta":{"origin":1062,"position":5},"title":"Subtotal Function in Google Sheets &#8211; How to Subtotal in Google Sheets?","author":"Varsha","date":"May 22, 2022","format":false,"excerpt":"The Google Sheets Subtotal function will enable users to subtotal the vertical range of cells using a specified aggregation function.\u00a0A total of 11+ functions are available in Google Spreadsheet, with the help of which we can perform the subtotal operation.\u00a0On this page, let us understand how to perform a subtotal\u2026","rel":"","context":"In &quot;Google Sheets&quot;","block_context":{"text":"Google Sheets","link":"https:\/\/sheetstips.com\/category\/google-sheets\/"},"img":{"alt_text":"SUBTOTAL function in Google Sheets","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/01\/subtotal-function-in-google-sheets3.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/1062"}],"collection":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/comments?post=1062"}],"version-history":[{"count":2,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/1062\/revisions"}],"predecessor-version":[{"id":4856,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/1062\/revisions\/4856"}],"wp:attachment":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/media?parent=1062"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/categories?post=1062"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/tags?post=1062"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}