Comments on: Excel FILTER function with formula examples

The introduction of the FILTER function in Excel 365 has become a long-awaited alternative to the conventional methods. Unlike the Filter feature that needs to be re-applied with each data change, Excel formulas recalculate automatically, so you need to set up your filter just once! Continue reading →

Comments page 3. Total comments: 341

  1. Hi, thank for very helpful article. I am running a sort filter formula to get the top 10 values of a column, but text is included in this column, so I don't get the top 10 values but all those lines that contains text. Could you please advise how I could ignore text and get the right results?
    Thanks in advance

    1. Hi!
      If you are using the FILTER function and you want only numeric values, use the ISNUMBER function in the conditions. If you describe the problem in more detail and write your own formula, I will try to give more accurate advice.

  2. Thank you for this post! It helped me in creating a dynamic array using Filter. I used Index though..Great job!

  3. I'm trying to filter to return all rows that have the same student ID...BUT, I want to match a Range of ids.

    I have
    =FILTER('[Most Recent Student Grades.xlsx]Current'!$F$2:$AG$12000,'[Most Recent Student Grades.xlsx]Current'!$L$2:$L$12000=F1)
    where F1 is a single student id and that gives me all the rows that match THAT student. THIS PART WORKS.

    What I need is to be able to specify a RANGE OF IDs not just a single ID and have the =FILTER return every row that matches any of the ids in that range.
    So, almost as though instead of F1, I could say AM2:AM30 and find all the rows that match the student ids in my list of student IDs (AM2 through AM30).

    1. I should have added: I know there are references to external spreadsheets that you cannot see--but that part works. The values in column "L" of that spreadsheet are the student IDs. And, again, there could be 5-8 rows for each student ID (a student could have 5-8 classes and each row represents a class). The F1 is just a cell into which I typed a single student ID so that I could make sure the FILTER function returned all the rows (classes) for that one student. What I need to do NOW is to be able to specify a range (list of student IDs) so that I can see all the students in a particular cohort on the same sheet.

        1. I'd tried the Advanced filter function before I asked for help as well. That doesn't "auto update" each time the source data changes - I have to re-apply the filter each time the source data changes.
          I want to be able to load ALL the data into one tab (The FULL DATA), and then (for each teacher) create another TEACHER tab that pulls all the rows for each of their list of students (where the list is stored on ANOTHER tab) and shows all the rows for each student. AND, the next week, when the grades are updated, to be able to updated the FULL DATA tab with the most recent info and have all the TEACHER tabs automatically show the new updated rows.

          So to accomplish =FILTER('FULL DATA'!A2:AG12000,'FULL DATE'!A2:A12000=991000303)
          EXCEPT THAT instead of a single ID (991000303) I can filter all the rows for a set of IDs, a la:
          =FILTER('FULL DATA'!A2:AG12000,'FULL DATA'!A2:A12000='LIST OF STUDENTS IDS'!B3:B45)

          I know the advanced filter will let me do the range...but then when the data changes, I have to re-apply the filter. Since I'm doing this for multiple people (who all need to look at a different set of IDs), I don't want to re-apply anything. And, the OR function is difficult to create and maintain when there are 25-45 different IDs each teacher would need to look at (hence the reason I'd like to have the IDs in a range of cells).

          2. I played with it further and was able to get it to work. Please disregard my last post; I am unable to remove it.
            Thank you for your time.

            1. TraceyAnn,

              I know that you've said you solved it, so I'm just leaving this here in case anyone else wants to try a different solution. This uses power query:

              Let's say you have a named range called FULLDATA with columns that you want to filter on called FD_criteria1 and FD_criteria2.
              Have another named range called CRITERIA with the columns criteria1 and criteria2, each listing the multiple values you want to include.

              The filtered output can be found by having a query with the following code:

              // beginning of query
              let
              // get our source data from named range FULLDATA
              Source1 = Excel.CurrentWorkbook(){[Name="FULLDATA"]}[Content],
              // turn the first row into headers and call the resulting table "FullDataTable"
              FullDataTable = Table.PromoteHeaders(Source1, [PromoteAllScalars=true]),

              // get the criteria data from named range CRITERIA
              Source2 = Excel.CurrentWorkbook(){[Name="CRITERIA"]}[Content],
              // turn the first row into headers and call the resulting table "CriteriaTable"
              CriteriaTable = Table.PromoteHeaders(Source2, [PromoteAllScalars=true]),

              // keep only rows from FullDataTable where the FD_criteria1 column value matches CriteriaTable's criteria1 column values
              // this filtered dataset is called Filtered_On_Criteria1
              Filtered_On_Criteria1 = Table.NestedJoin(FullDataTable, {"FD_criteria1"}, CriteriaTable, {"criteria1"}, "New_Column_Name1", JoinKind.Inner),

              // keep only rows from Filtered_On_Criteria1 table where the FD_criteria2 column value matches CriteriaTable's criteria2 column values
              Filtered_On_Criteria2_also = Table.NestedJoin(Filtered_On_Criteria1, {"FD_criteria2"}, CriteriaTable, {"criteria2"}, "New_Column_Name2", JoinKind.Inner),

              // the two previous steps created additional new columns, so remove them
              OutputTable = Table.RemoveColumns(Filtered_On_Criteria2_also,{"New_Column_Name1", "New_Column_Name2"})
              in
              OutputTable
              // end of query

              1. Use filter function with isnumber(xmatch(range of data I’d,range of lookupid)

              2. You may use list of I’d,s from a range instead of a single I’d in condition

      2. I DID look at the AND and OR before I posted. That seems to require me to make a list of OR and then edit that list IN THE FORMULA each time I need to add or remove a student to the list. I don't want to have to do that -- I want to be able to use a range of data to accomplish the "OR". So that, if I need to add or remove a student, I can just edit the IDs on the sheet and not the formula.

        1. I currently have 35 different student IDs that I need to include. And, I would like to allow others to use the same set-up for THEIR lists of students; having a range that can be created makes much more sense than trying to add 30-45 "OR" statements into a formula and then having to go back and edit whenever a student adds/drops from our caseloads.

  4. Hi Peeps,

    Need help here with our Google sheets.

    What we wanted to happen is when we filter or type in the word Pass the Name of the person will appear on the next tab of the same sheet and it will be placed on the last row.

    Is that possible?

    Your assistance is very much appreciated :)

  5. I used filter option to get a few details from one sheet(sheet A) to another sheet(sheet B), but I want more details to be added in the rows in the sheet B.
    Each row has a date column,
    when I add older dated rows with the filter option, the row sequence in sheet B changes( the rows shift up or down as per the date)
    the rows are shown date wise but the additional details added that were added manually still remain in the same row where they were added before.
    how can I add this manual data to the same row as the data that has come through the filter. And have it remain with the same data even if more older dated rows are added.

  6. I have an issue and cannot find the solution, or I am looking for the wrong wording. but i hope somebody does have a solution.

    In excel 365 i would like to have multiple tabs with the same information (continually updated) but filtered in different ways.
    Workflow is as follows:
    Tab 1 coordination is assigning a task to one of many trucks (filtered on date/time)
    Tab 2 trucks see the task and after completing fil in the completed work (filtered on date/time/truck number)
    Tab 3 finance can see al data (filtered on date/customer)

    My main issue is with the filtering, I used VBA to update the filters but that doesn't work in 365. And in the =filter function you can't enter any information back to the database.

    Is there any way to get this working or is this something excel isn't made for?

    many thanks
    Misha

  7. Hi,
    Thank you for this awesome post.
    Is there a way to sum based on the values of a column (rather than a single value in cell e4) ?
    i.e. I have a filtered list with column A containing multiple text references and values in Col B and I would like the filtered list to sum the values in B where the text on multiple rows in ColA are the same

    A |B
    Day1|50
    Day1|30
    Day2|10
    Day2|15

    but I need the filtered list to output the following aggregated list
    A |B
    Day1|80
    Day2|25

    Cheers
    Gav

      1. Thank you Alexander. Really appreciate the timely response.

        Those suggestions didn't include the filter function though.
        Really all I'm trying to do is modify your SUM "Total wins" example above "=SUM(FILTER(C2:C13, B2:B13=F1, 0)" but instead of using a single value in F1 to sum, I need to perform a sum of all values from the Wins in column D (in your example) for each of the Groups in column C

        So the spill output of the filter formula would result in:
        A|8
        B|8
        C|8

        Can this be done with Filter ?
        Thanks again
        Gavin

    2. Sorry, I meant "(rather than a single value in cell F1)"

      1. This is my current formula and I need the sum of col # 23 where col 12 is the same (they are sorted)

        =FILTER(INDEX(Tbl_AllocnNewUnconverted,SEQUENCE(ROWS(Tbl_AllocnNewUnconverted)),{12,3,21,22,23}),Tbl_AllocnNewUnconverted[Exclusions]="Allocations New",0)

        1. Hi!
          It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.

  8. Hie,

    Need experts help!

    I have simple data but complex calculation.

    From Date To Date Level Size Bands Local Regional National
    01-01-2022 31-12-2022 Premium 0 to 500 gm 25 39 60
    Advanced 0 to 500 gm 25 39 60
    Standard 0 to 500 gm 31 40 61
    Basic 0 to 500 gm 37 45 65
    All Levels 500gm to 1 kg 13 17 25 (Fee per Each 500gm upto 1 kg)
    All Levels 1 kg to 5 kg 21 27 33 (Fee per Each kg upto 5 kg)
    All Levels 5 kg + 12 13 16 (Fee per Each kg after 5 kg)

    Requirement is to calculate the total fee for specific zone (Local/Regional/National) with current level (Premium/Advanced/Standard/Basic) for total weight of item ordered in particular date range.

    Example: If someone ordered an item weighing 4.5 kg in total on 23-04-2022 which is to be delivered at national level and level is advanced, total fee will be - 60(0-500gm) +25 (500-1kg) + 33*4 (each kg upto 5 kg) = $ 217.

    New values of fees will be there as per dates ranges. Size bands can also vary based upon dates. Levels & zones are constant. Fees to be calculated based on ordered date. Cannot use VBA. Using Online excel. Formula will be more suitable.

  9. Hi,

    First of all, thanks a lot for all this information, but I need to go a little bit further than this article. I need to filter the same data but in multiple "tables" (side by side). I want to start the second "table" with the end of the data of the previous "table".

    Each "table" must contain 81 lines and 6 columns. I am able to create 1 table due to the information on this page, with the combinaison of Index, Sequence and Filter. But once I limit the filter with the Index formula, I can't start a new "table" with the last data used in the previous "table".

    For example :

    Primary data Table 1 Table 2
    A B C D E F G H I
    Name Group Wins Name Group Wins Name Group Wins
    Aiden A 0 Andrew C 4 Charlotte B 2
    Andrew C 4 Betty B 1 Mason A 4
    Betty B 1 Caden C 2
    Caden C 2
    Charlotte B 2
    Emma C 0
    Isabella A 0
    Mason A 4

    Is there a solution to this problem?

    P.S. My primary data is listed in a various (pretty high) number of lines but is fixed with 6 columns. And I want to display the filtered info side by side with a fixed number of lines.

    Thank you,

    Joseph

    1. Thank you for your quick response. But these solutions don't work in my case. I found a complicated way to get the job done and it works !

      Thanks

      Joseph

  10. Hi,

    Is there way a way to filter using multiple criteria if one criteria is in the column headers and the other criteria is in the row?

    Example:
    Program 1 - January | Program 2 - January | Program 1 - February | Program 2 - February
    Alex Accepted No Response - Accepted
    Adam No Response Accepted No Response No Response
    Bob No Response No Response - -

    And I want to be able to combine all the results for Program 1 by name:

    Program 1 | Program 2
    Alex Accepted, - No Response, Accepted
    Adam No Response, No Response Accepted, No Response
    Bob No Response, - No Response, -

    Thank you!

    1. Hi!
      All the necessary information is in the article above. Use nested FILTER functions. Use a vertical filter and then a horizontal filter.

  11. Hello,

    I am using the INDEX+FILTER to return specific rows and it works quite well. Is it possible though that I can avoid doubles?
    For example if my data is Anna, John, John, Steven and I want to return the first 3 rows matching my criteria, Is there a way to avoid the second John and return Steven instead?
    The Formula that I am using is =INDEX(FILTER($B$2:$D$8,$E$2:$E$8=L1),row number)

      1. Thank you so much! Couldn't find it anywhere. It works just perfect <3

  12. Hello,
    id like to use a filter function to return multiple rows matching a certain criteria from another spreadsheet. I used the formula but it's only returning the first cell of the rows.

    1. Hi!
      How can I guess which formula you used? If you describe the problem in more detail, I will try to help.

  13. Hi, i'm trying to use the "Filter multiple columns in Excel".

    I can't manage to figure out how i'd go about with the following scenario:

    For one of the multiple criteria, i want all of the values to be included. For example, there are 10 cities and instead of chosing 1, I want all 10 to be displayed, subject to the other criteria being met. And I also want the ability to then go and switch back to 1 specific city.

    Basically, if i select "All", all show up. If I select "Madrid", only Madrid shows up conditioned to the remaining criteria being met, hence I'd need to implement this within the "Filter multiple columns in Excel" structure.

    Does anyone know how to do this?

    1. Hi!
      You can filter values by multiple conditions using the FILTER function. Please read the above article carefully.

  14. Somehow "non-adjacent filter" trick didn't work for me. It filtered out rows not columns
    I'm using MS Office 365

    So I had to make some additional formula changes, I TRANSPOSE-d the first filter result, then in second filter I filtered out "my columns" and then I TRANSPOSE-d it back.

    =TRANSPOSE(
    FILTER(
    TRANSPOSE(
    FILTER(A2:C13, B2:B13=F1)
    ),
    {1,0,1}
    )
    )

    Maybe it is helpful to someone else too.

  15. If I used filter formula to selected to pull data from column a-d, and I added columns e-o to new sheet so I can add additional information. I have a condition that if the person "accepts" services the information will filter in to new data, but sometimes when click accepts later in the mail sheet, it will still add it to it's original spot and will throw off all the data i have entered in column e-o. Is there a way to link the columns so the rows don't mess up
    This is the filter formula I am using.

    =FILTER({'2021-2025 Students'!$B$2:$B$221,'2021-2025 Students'!$E$2:$F$221},'2021-2025 Students'!$H$2:$H$221="Accepted",'2021-2025 Students'!$G$2:$G$221"MDT",'2021-2025 Students'!$I$2:$I$221="YATV")

    1. Hi!
      Sorry, I do not fully understand the task. My guess is that you are copying the file into an email and then changing it. These changes cannot be saved as they are two different files.

  16. I have a sheet with patient longitudinal data. Patients were followed up for a year. Not all of them attend all visits. Some have two and some five visits. The ID is repeated depending on how many visits one attended. ID is in column A and, type of visit in column B. I want to extract values in column G if the participant attended a visit at month 6.

  17. Hi. Please is there a complete video on this lesson?

  18. I'm wanting to filter a column by Data Type 2 or ISTEXT.
    Meaning to include only cells that are of that type.
    I can't seem to find the syntax for this.
    Thanks for any advice.

    1. Hello!
      Create an additional column with the ISTEXT formula. Set a filter on this column.
      You can use the FILTER function.
      For example,

      =FILTER(A2:C13, ISTEXT(B2:B13))

      I hope it’ll be helpful.

      1. Thanks very much!
        This solved my issue; and so quickly.
        It always seems so simple once you have the right form.
        I was getting a bit confused because I was trying to filter within in a single column.
        My variant of your suggestion ended up similar to:

        =FILTER(A2:A13, ISTEXT(A2:A13))

        (i.e. removing numeric data types from a single column)

        That works exactly as I needed; Thanks

        Also:
        If there is a (formula) way to append a second filtered column under the
        first result list (vertically), I would be very interested in how to do that.

          1. Thanks again,
            I will pursue that approach.

  19. Amazing forum, thanks for all the helpful tips!

    I keep running into a condition which I can't solve...

    I'm trying to use the FILTER function (which works fine) but I nest it into a COUNTA because I want to count the number of items inside the filtered result list (the list is a list of alphanumeric codes hence my attempt to use COUNTA).

    COUNTA( FILTER( array, criteria, 0))

    The problem only occurs when there is a null result, it always returns 1 (instead of blank or 0).

    I have also tried using a COUNTIF( FILTER( array, criteria, 0) , TRUE) in an attempt to count the TRUE only, but it won't let me enter that formula at all (true criteria at the end seems to not be accepted no matter what I try, e.g. "1", "True", "False", etc...)

    I believe Excel is trying to tell me that I should not be using FILTER when I want to count like this....

    I'm sure many people are already solving this using another method, I just haven't figured it out yet.

    Any help?

    1. Hello!
      COUNTIF can only count values in a range. It does not work with other formulas. To conditionally count values, try using the SUMPRODUCT function as described in this tutorial. I hope it’ll be helpful. If something is still unclear, please feel free to ask.

  20. Hello,

    I am working with Filter Function, but I realized that it doesn't work when the cells I am working with have a space between words or special characters (e.g., "&"). The result is always #N/A in such scenarios. Is there a way to solve this challenge?

    I am working with lots of data about 10,000 rows so I can't manually remove the space or special characters.

    1. Hello!
      Unfortunately, I couldn't reproduce your problem. For example, the value "C & C" is filtered correctly. Give an example of your data and the formula you are using.

  21. Sir, I want to ask whether data from non-adjecnt column can be filtered (via filter function) to a specified rannge of columns (that is output results should be in a slected range of column or columns rather than default inwhich data from different columns filterd based on number of columns from which it was driven?
    Is it Possible? and if possible then kindly share example formula

    Thanks & regards,

    1. Hi!
      If I understand your task correctly, pay attention to the following paragraph of the article above: Example 2. Filter non-adjacent columns.
      Hope this is what you need.

  22. Hi,
    i wants write multiple Value at one Cell of ie. B2 Cell i am writing value of many countries like pakistan Iran India . than how can put formula for that

  23. Thanks for the article! Just wondering if the FILTER function can be used with a large number of "include" options. I understand how it would work for a "this or that" type scenario, but I've got a large dataset and want to get the results for about 200 different options. Is there a better option than
    = FILTER (Range, (Range = Option 1) + (Range = Option 2) + (Range = Option 3)+ ... + (Range = Option 200))

    1. I am hoping I understand you correctly and am going to offer what I think is a solution to your question as I just figured this out myself and was very pleased with how it worked.

      If your "Options" were arranged as a column of values (ie: E1:E200) per se, you could use the following formula to filter your data:

      = FILTER(Range, ISNUMBER(MATCH(Range,E1:E200,0)) )

      The MATCH function will return numbers in an array the same size of the 'Range' on lines where the 'Options' match. Then the ISNUMBER will convert those matches to a value of TRUE.

  24. Thanks for the article! I want to know if it's possible to use the Filter function (or similar) to actually apply the filter to the original table to display the results, rather than reproduce the results in a new set of cells like in your example. I am working with tables with 10+ columns so it's not practical to have the results side-by-side like in your example.
    I know this can be done using VBA (the code applies an autofilter with the criteria entered in a search box/linked cell), but I was hoping to be able to work with non-macro enabled workbooks if possible.
    Any tips would be most appreciated!

      1. Thanks for your reply. I get that you can use the Excel filter tool to do that, but then you have to manually enter/clear your filter each time you want to search. I work with large glossaries, so I just want to search for a term in one column (e.g. in an activex text box), and then it will apply the filter in real time as I type. I can do this using VBA (see working code below), but I'd like to do this without having to save and share macro-enabled workbooks with colleagues.

        I hope that makes sense!

        How it works in VBA:
        I insert an activex text box and link it to cell C1. I then assign the below macro to it. When I enter text in the box, it is replicated in cell C1 and the column is filtered based on the text in cell C1.

        Private Sub TextBox1_Change()
        Application.ScreenUpdating = False
        ActiveSheet.ListObjects("Monographs").Range.AutoFilter Field:=1, Criteria1:="*" & [C1] & "*", Operator:=xlFilterValues
        Application.ScreenUpdating = True
        End Sub

  25. I am trying to filter based on various dropdown menus which would correspond with columns in my dataset. The problem I'm having is I want to include an "All" option for each dropdown menu. I've been able to accomplish this with a series of if statements that gets complicated quickly. Is there an easy way to do this?

    For example if my dataset looks like this:

    state, age, gender, score

    Can I use dropdown menus and the filter function to allow for these combinations:

    NY, all ages, men, scores>50
    all states, <18, women, all scores
    etc...

      1. Yes, but not really for this purpose. I am building a dashboard using a filtered subset of the data. The user has dropdown menus (from data validation) to select their filters, then I use the filter function to select the data that the charts are built off of. I'm trying to determine if the filter function is sophisticated enough to do this.

        1. Hello!
          The FILTER function can also perform this task. Use the examples in the article above. If there is a specific question regarding the formula, I will try to help.

          1. Thank you. I am still having a hard time getting it to work properly. I have this function that works fine:

            =IF(Dashboard!V3="ALL",SORT(FILTER(A2:P665,(G2:G665>0)),8,1),SORT(FILTER(A2:P665,(G2:G665>0)*(C2:C665=Dashboard!V3)*(I2:I665=Dashboard!$X$3)),8,1))

            I want to be able to remove the if statement, then add more criteria like this:

            =SORT(FILTER(A2:P665,(G2:G665>0)*(C2:C665=Dashboard!V3)*(I2:I665=Dashboard!X3)),8,1)

            The problem is when Dashboard!V3 or Dashboard!X3 is "All" instead of an exact match for something in the respective column.

            1. Hi!
              It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.

              1. Thanks for your help. I was able to figure it out using if statements that didn't get too complicated. In case anyone else has this problem, here was my solution (which assumes there are no blank cells):

                =filter(A:D,if(F1="ALL",A:A"",A:A=F1) * if(F2="ALL",B:B"",B:B=F2) * if(F3="ALL",C:C"",C:C=F3) * if(F4="ALL",D:D"",D:D=F4))

              2. I can appreciate that, it's why I tried to come up with a generic example in my original post. Can I go back to that example? If the data is in columns A-D is state, age, gender, score. I want to be able to filter on some or all of these in a formula. in cell F1 is the state I want, F2 is the age, F3 is gender and f4 score. I believe I can write a formula as such:

                =filter(A:D,(A:A=F1)*(B:B=F2)*(C:C=F3)*(D:D=F4))

                However what do I do if one of the values in F1-F4 has "All" as an option? can I leave it blank? Is there another workaround that doesn't include nested if statements? Or does the filter function simply not work in this instance?

                Sorry for so many questions, I'm really hoping I can get this to work and your webpage has been very helpful.

              3. Hello!
                If you have a condition on the value in cell F1, then the IF function is used to filter based on the condition. If F1 is empty, then the first filter is used, if it contains a value, the second filter is used.

  26. Hi!

    Thank you for your guide, it is much more useful than many others. Yet i have a problem. Is there any way how to edit filtered data? My situation is: I have a storage, trying to make some kind of "frontend" for users and i need to edit already filtered data. You search for item, you see how many is somewhere, you fill out how many you took from that amout and you update "database". I cant find any useful informations about it. Can you help me please?

    Thank you VERY much,
    Martin

      1. Thank you, I will try it out :)

  27. In your example under "Filter with multiple criteria (AND logic)" is it possible for the formula to ignore blank inputs.

    For example if I only place a value in Groups of A and leave Wins blank it shows all the A results, but if I add a value to wins then it narrows it down. And if I remove the Group value and only have wins it shows values of all wins above "2" ignoring the Group value since it's blank.

    I cant seem to figure this out.

    1. Hello!
      Unfortunately, I was unable to reproduce your issue. If you remove the Group value, the result is empty.

      1. Alexander, thank you for your response.

        I should explain better. If I had an inventory of cars at a dealer ship in a sheet with Make, model, type, etc... would I be able to create a filter where if I have 2 "And" values. Make and Type.

        example:=FILTER(invo,(invo[Make]=F2)*(invo[Type]=F3))

        Table=invo,
        *Make *Model *Type
        Chevy-Malibu-sedan
        Chevy-1500-pickup
        Chevy-2500-pickup
        Ford-explorer-suv
        Ford-F150-pickup
        Ford-F250 pickup
        Toyota-Corola-Sedan
        Toyota-Tundra-pickup
        Toyota-Tacoma-pickup

        If I only place 1 value in the filter such as "pickup" it will give me the a list of pickup in my inventory with mix of all 3 makes.

        But, if I type 2nd value of Make, I can narrow to all the "pickup" And "Chevy"

        Currently it only filters if both values are added.

        When running a Query in google sheets this seems to be the default "And" behavior but If I leave a value blank under the excel =filter it wont give results. If i use the "Or" "+" i get a mixture which can be used for certain sheets but not to narrow down inventory search.

        2. I was able to replicate with a bunch if if statements. It gets messy if you have more than two variables.

  28. I have values in columns A to Z
    Now, I want to get values from column A, B, C, K, N, and P if there is a "error" value in column Z. In column Z there are different values such as error, ready, not ready and so on. I need values from above mentioned columns if there is a "error" value in column Z. Please help me out.

    1. Hello!
      The FILTER function applies to only one range. So use it multiple times.

      =FILTER(A2:C7,Z2:Z7="error")

      Please re-check the article above since it covers your case.

  29. Thanks for sharing all this info, it's very complete and valuable.

    I am using Google Sheets for some years now due to the company's requirements, and after learn how the Filter formula works, I use it for everything, but I know the older versions of Excel didn't have this option, and with this article I could learn all that I needed about this amazing function in Excel.

    Thanks for this amazing job!

  30. Hello
    Thanks for publishing such a valuable article, but I still have a question I hope you can help me with.

    If we use the table of data you have used in this article (Name, Group, and Wins), what I want to do is filter out Group B and also determine the largest value for Group B. In other words, I want the filter to be able to determine that the largest value in Group B is 3 and that it belongs to Oliver.
    When I set my filter function to filter for Group B AND the largest value, I get an error as my filter is finding the largest number in the Wins column (which is 4 from both Groups A and C) and it does not correspond to Group B, therefore, the error.
    I want to keep the filter dynamic and thus wanted to use the filter function, but I can’t seem to make this work even though it sounds simple. Essentially, I need the filter function to look only at the number of wins in Group B and then determine the largest value of wins and then spit out Name, Group and the number corresponding to the largest wins.
    Any help would be greatly appreciated. Thanks for your time.

    1. Hello!
      You can use the MAXIFS function to add another condition to the filter. Pay attention also to the paragraph in the article above - Filter with multiple criteria (AND logic)

      =FILTER(A2:C13, (B2:B13=F1)*(C2:C13=MAXIFS(C2:C13,B2:B13,F1)),"No results")

      I hope my advice will help you solve your task.

  31. Hey do you know why FILTER formula is not working when I open the file on OneDrive? The formula works perfectly when the file is opened on local Excel. No error is shown. I have no idea what wrong, could you help me on this. Thank you.

    The formula that I key is as follow:
    IFERROR(IF(FILTER(D55:D69,G55:G69<0)=0,"",FILTER(D55:D69,G55:G69<0)),"")

  32. Hello,
    After creating a filtered table (using this filter function) I need to extract the resulting table into a data table in which every cell would be filled with the data and not a reflection of the formula used.
    Is it possible and how?

    Thank you in advance.

  33. Hello,

    I've been trying to filter a data set that will return results based on two user input options (example: InpA and InpB). I tried using the information from the "Multiple AND/OR" article, but the formula that I use seems to exclude the results that contain the inputs.

    "InpA" data is found in the first column, however, "InpB" data can be listed in one of 22 columns per row. I thought that I might need to create "OR" statements for the function to include each column (example table is below). Any insight would be greatly appreciated! Thank you!

    The formula structure is: (FILTER(TT_Merge!C5:Z148,(TT_Merge!C5:C148=C5)*((TT_Merge!D5:D148=Dashboard!E5)+(TT_Merge!E5:E148=Dashboard!E5)+(TT_Merge!F5:F148=Dashboard!E5)+(TT_Merge!G5:G148=Dashboard!E5)......+(TT_Merge!Y5:Y148=Dashboard!E5)),"No Accounts Found"

    C1 C2 C3 C4 C5 C6 .....
    Assay ST-1 ST-2 ST-3 ST-4 ST-5 ST-6
    R1 ASA STA STB STC
    R2 ASB STA STC
    R3 ASC STB STA STD STG
    R4 ASF STF STC

    1. Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work.
      Here's an example of a formula that works. I assume it is similar to your conditions.

      =FILTER(A2:D13, (B2:B13=F2)*((C2:C13=1)+(D2:D13=2)), "No results")

  34. Hello Everyone,

    Anyone help me out, I have mentioned below my question.

    If A1 is ABC then takes filter list value from 'sheet 1' and if A1 is XYZ then takes filter list value from 'sheet 2'

    I have tried this formula with IF function but I get an error.

    1. Hi!
      I can't guess which formula doesn't work for you. But this formula works:

      =IF(A1="ABC",FILTER('1'!A2:C13, '1'!B2:B13='1'!F1, "No results"),FILTER('2'!B1:M3,'2'!B2:M2= '2'!B6, "No results"))

  35. Hi Everyone,

    I have an Excel file for daily reports of a project, each day has a sheet, named the date of that day, so we add a sheet every day.
    in the "summary" sheet I want to have a table that automatically:
    1 - read the date from a cell in the same table.
    2 - look for a sheet with the name of that date
    3 - read and bring a value from a cell of that sheet into this new table cell
    Can you please let me know if you know how to do it?
    Thank you

  36. Hi,

    I am using the formula FILTER(IF(Data!A4:N143="","",Data!A4:N143),Data!F4:F143=A1) this is awesome and really working well. I do have one issue though and that is that I am getting the #VALUE! error on cells where the data its pulling through contains a lot of text. If I amend the cell to a few words it pulls through in the filter with no issues.

    Is there a way that I can make it pull through all the text in the cell?

    Thanks

  37. Can the array be from another workbook on my server?
    The workbook on the server updates every 30 minutes with fresh data, I would like to give my employee a lookup spreadsheet that extracts from the file on the server.

  38. Hello,

    For my work, i need to extract and filter information from 2 columns, but count how many times no information was added.
    Eg:
    (Column J 2-300) (Column L 2-300)
    Acknowledged by Context
    Guard.1 Information added
    Guard.2 (BLANK)
    Guard.1 (BLANK)
    Guard.3 Information added

    I have attempted to use the Filter formula, unique formula and xlookup formula, but i am getting no success.
    I am unable to use a pivot table as i need to be able to extract the information for the day into a separate table, then export this information to a separate tab within the same spreadsheet.

    Do you have any ideas on how this can work??

    1. Hello!
      To extract rows with empty cells in a column, you can use a formula like this:

      =FILTER(A1:C100,ISBLANK(B1:B100))

      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. Hi Alexander,

        The issue is that not only do i need to filter to locate the blank spaces, I also need to filter to match the guard who has left the area blank (not added context) and count them to place into another table.

        so the end goal is once i enter the raw data, with using the formulas, to fill in a graph which will be below:
        GUARD NAME - NO CONTEXT ADDED
        Guard.1 - (amount of no context added)

        Regards

        1. Hi!
          If I understood correctly, your summary table has 2 columns. You cannot create it with one formula. With the formula I gave you, you can fill in the first column.

          =FILTER(A1:A100,ISBLANK(B1:B100))

          To fill the second column, the COUNTIFS function can be used:

          =COUNTIFS(A1:A100,D1,B1:B100,"")

          Hope this is what you need.

  39. Hello and thank you for a great article. I am trying to do a filter where I can use and, and or in my logic. Let me see if I can make sense of what I want. Here is my table called roster with 4 columns:

    Column A Column B Column C Column D
    Input 1 User 1 Data 1
    Input 2 User 2 Data 1
    Input 1 User 3 Data 1

    So here is what I want the outcome to be. If column a equals 1 and Column C equals Data 1 or Column D equals Data 1then list Column B in Column E. So it would look like this on Column E (the columns above are in a table labeled roster)

    Data 1
    ____________
    User 1
    User 3

    Here is my formula I have but I keep getting all three users

    FILTER(Roster[[Column B]:[Column B]],(ISNUMBER(SEARCH("Input 1*",Roster[[Column A]:[Column A]])))*(ISNUMBER(SEARCH("Data 1",Roster[[Column C]:[Column C]])))+(ISNUMBER(SEARCH("Data 1",Roster[[Column D]:[Column D]]))),"No Users")

    1. Hi!
      I cannot test your formula as it contains references to your data tables. Here's a sample formula for you:

      =FILTER(B1:B3,(A1:A3="Input 1"*((C1:C3="Data1")+(D1:D3="Data1"))))

      There are 3 conditions used here.

  40. I'm trying to use drop down lists to filter a table on two variables (teacher and period) so I'm trying this:

    =FILTER(Table2[Student Name],(Table2[1]=B1))

    This returns the column "Student Name" from the table "Table 2" based on the data in column "1" matching the data in cell B1.

    What I want to do is have a second cell, C1, control the column name "1" but I can't figure out the right way to format it.

    1. Hello!
      If I understood the problem correctly, you can use different FILTER formulas for different C1 values using the IF function.
      For example:

      =IF(C1=1,FILTER(A2:C13, B2:B13=F1, "No results"),IF(C1=2,FILTER(A2:C13, C2:C13=F1, "No results"),"0"))

  41. I have a column of text values and wish to filter based on the length of each value. I only want to return values which are 5 characters long. I realised I could do this easily by creating a second column which contains the length of each value in the first column, but it would be more elegant to do this without having to create additional meta-data.

    1. Hello!
      To set a filter conditionally, you can use the recommendations from this article.
      The formula might be something like this:

      =FILTER(A1:A10,LEN(A1:A10)<5)

  42. Is there anyway for the "include" part of the formula to be based on multiple criteria in the same column? Using the first example in your article, could I make the filter array return all of the "b" and "c" into one output?

      1. Oaky - sorry - I read that again carefully. I had missed the "Or Logic" section initially. Thanks so much - that scenario fits perfectly!

  43. Hi!

    Is it possible to use this formula to filter multiple sheets? Do i need to apply it together with VBA loop through multiple sheets?

    Thanks.

  44. I'm trying to filter the data but be able to edit the data that's filtered. With dynamic filtering it's a preview of the data in the table. How do I edit the data that's filtered with dynamic filter?

  45. Hi, Thank you for such wonderful explanations with examples.
    I have a question related to limiting the number of rows returned by FILTER.

    In my code I have used something like this to return data consisting of limited columns (columns 1 and 3 in code with {1\3}) but with unlimited rows.

    How and where should my condition for limiting the number of rows go in the following code

    =FILTER(INDEX(mD!D9:F19;SEQUENCE(ROWS(mD!D9:F19));{1\3});(mD!X9:X19=TRUE)).

    I tried adding the rows before the {1\3}, but it did not work.

    Since the code I have has construction FILTER(INDEX...) different from the example here INDEX(FILTER...), I am not able to interpret the solution

  46. Thank you for the update and this wonderful article. If I could ask a question:

    Is it possible to loop through a column of values for the criteria? For example, if I have something like this:

    Sheet2.Range("A2").Formula2 = "=FILTER(Sheet1!A2:J100,Sheet1!B1:B100=F1,"")"

    Is there anyway of looping through the values in the Column F directly or say in a VB macro?

    A VB macro might look like this:
    Sub Test()
    With ActiveSheet
    Sheet2.Range("A2").Formula2 = "=FILTER(Sheet1!A2:J100,Sheet1!B1:B100=F1,"")"
    End With
    End Sub

  47. The data I'm filtering can have multiple lines within a single cell. How can I set the workbook to autoFit the height when the data is pulled in?

  48. Can you please help me to "Filter on blanks". The data set has
    Column A has a list of Names
    Column B has performance grades 1-5 (of which some will be blank, so if column B is blank return the corresponding Name.

    =FILTER('Line by Line Data'!$A:$A,'Line by Line Data'!B:B="","") currently returns the following. What I am after is the data without 0

    0
    Bob Smith
    0
    0
    0
    0
    Jane Evans

  49. Can you please help me to "Filter cells containing specific text" from multiple worksheets or multiple tables?
    For example, FILTER(array, ISNUMBER(SEARCH("text", range)), "No results"). How can I added multiple tables that are in various worksheets to the above formula?

    Hope someone helps. Thank you

    1. Hello!
      The FILTER function works only with the data of the current sheet. You can combine data from multiple sheets into a current sheet using the Copy Sheets tool.
      It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
      To work with multiple tables on one sheet might be helpful - "Example 1. Filter multiple columns in ExcelExample 1. Filter multiple columns in Excel". Read if above.

  50. Hi Alexander - thanks for the article, I've found the FILTER function very useful for parsing data.

    I'm trying to apply your does not equal logic to exclude rows of data that contain specific names. For example: =FILTER(A1:H2500,(H1:H2500(("Acme Inc."))))

    This works well for a single instance like "Acme Inc.", but I would like to nest this so that I can exclude several companies tagged within column H (eg "Acme Inc."; "Smith Inc."; "Alex Inc.";). Is there a way to do this? I've tried creating an OR statement with the names with no success.

    Any help is much appreciated.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =FILTER(A1:B30,(A1:A30<>"Acme Inc.")*(A1:A30<>"Smith Inc.")*(A1:A30<>"Alex Inc."))

      Hope this is what you need.

    2. To clarify, the function above should read =FILTER(H1:H2500,(H1:H2500(("Acme Inc.")))) ...regardless I have not found a way to nest multiple names from the same column.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)