Monday, June 6, 2022

Filter function

The FILTER function allows you to filter a range of data based on criteria you define.

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Note: This function is currently available only to Microsoft 365 subscribers.

In the following example we used the formula =FILTER(A5:D20,C5:C20=H2,"") to return all records for Apple, as selected in cell H2, and if there are no apples, return an empty string ("").

FILTER function - Filter Region by Product (Apple)

The FILTER function filters an array based on a Boolean (True/False) array.

=FILTER(array,include,[if_empty])

Argument

Description

array

Required

The array, or range to filter

include

Required

A Boolean array whose height or width is the same as the array

[if_empty]

Optional

The value to return if all values in the included array are empty (filter returns nothing)

Notes: 

  • An array can be thought of as a row of values, a column of values, or a combination of rows and columns of values. In the example above, the source array for our FILTER formula is range A5:D20.

  • The FILTER function will return an array, which will spill if it's the final result of a formula. This means that Excel will dynamically create the appropriate sized array range when you press ENTER. If your supporting data is in an Excel table, then the array will automatically resize as you add or remove data from your array range if you're using structured references. For more details, see this article on spilled array behavior.

  • If your dataset has the potential of returning an empty value, then use the 3rd argument ([if_empty]). Otherwise, a #CALC! error will result, as Excel does not currently support empty arrays.

  • If any value of the include argument is an error (#N/A, #VALUE, etc.) or cannot be converted to a Boolean, the FILTER function will return an error.  

  • Excel has limited support for dynamic arrays between workbooks, and this scenario is only supported when both workbooks are open. If you close the source workbook, any linked dynamic array formulas will return a #REF! error when they are refreshed.

Examples

FILTER used to return multiple criteria

In this case, we're using the multiplication operator (*) to return all values in our array range (A5:D20) that have Apples AND are in the East region: =FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"").

Using FILTER with the multiplication operator (*) to return all values in our array range (A5:D20) that have Apples AND are in the East region.

FILTER used to return multiple criteria and sort

In this case, we're using the previous FILTER function with the SORT function to return all values in our array range (A5:D20) that have Apples AND are in the East region, and then sort Units in descending order: =SORT(FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),""),4,-1)

Using FILTER with the SORT function to return all values in our array range (A5:D20) that have Apples AND are in the East region, and then sort Units in descending order.

In this case, we're using the FILTER function with the addition operator (+) to return all values in our array range (A5:D20) that have Apples OR are in the East region, and then sort Units in descending order: =SORT(FILTER(A5:D20,(C5:C20=H1)+(A5:A20=H2),""),4,-1).

FILTER and SORT together - Filter by Product (Apple) OR by Region (East)

Notice that none of the functions require absolute references, since they only exist in one cell, and spill their results to neighboring cells.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See Also

RANDARRAY function

SEQUENCE function

SORT function

SORTBY function

UNIQUE function

#SPILL! errors in Excel

Dynamic arrays and spilled array behavior

Implicit intersection operator: @

1 comment:

  1. BUSINESS LOAN FORM INDIABULLS HOUSING FINANCE LIMITED

    Have you been looking for financing options for your new business plans, Are you seeking a loan to expand your existing business, Do you find yourself in a bit of trouble with unpaid bills and you don’t know which way to go or where to turn to? Have you been turned down by your banks? Dr. Mark Thomas Loan Grants says YES when your banks say NO. Contact us as we offer financial services at a low and affordable interest rate of 3% for long and short term loans. Interested applicants should contact us for further loan acquisition procedures.

    Our services include the following:
    *Refinancing Loans
    * Car Loan
    *Truck Loans
    * Home Loan
    * Mortgage Loan
    * Debt Consolidation Loan
    * Business Loan [secure and unsecured]
    * Personal Loan [secure and unsecured]
    * Students Loan and so many others.

    Ronnie Finance Ltd
    Email markthomasfinanceltd@gmail.com
    Email apply@ronniefinance.ltd
    Whats App +919667837169
    http://ronniefinance.ltd/fastloan
    Dr. Mark Thomas

    ReplyDelete