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 2. Total comments: 344

  1. In a specific coloum I want to filter numbers totatl of which is equal to specific number e.g, a coloum containing data like 10,20,30,40,50,60,70,80,90,100 and I want to filter cell value total of which is eqaul to 130.

  2. Hello,

    If have the below column with the listed rows. Each cell basically contains a string.
    COL-A:
    P1-P2-A-P3
    P1-P2-B-P3
    P1-P2-R-P3
    P1-P2-Z-P3

    I also have the below column and corresponding rows
    COL-E:
    A
    B
    Z

    I want to use the FILTER function to filter all the rows of which the string in COL-A contains A or B or Z (= the values in the COLE-E range)
    I'll need/I want to use the SEARCH function.
    I want to combine the FILTER and the SEARCH function to return (=filter) the correct rows in COL-A
    I don't want to hardcode anything in the FILTER function (e.g. use "or" (+) syntax as explained in this article), nor in the SEARCH function, just work with the ranges.

    Could anyone describe how to get this to work, because I didn't succeeded yet.

    Kind regards

  3. An online Excel level 3 course, unfortunately created in 2016, refers to 'advanced filtering' and using an additional header to this.
    Criteria Range being the Additional Header labels & any Criteria Ranges specified; followed by the vital blank row. Is this format now redundant in Microsoft365 Excel?

  4. I am trying to filter a list of IP addresses (3700) into 3 categories. Each category contains specific IP addresses. Is there a function that will filter the full list down to only IP addresses that are included in a single category?

    For example, if I have 5 IPs that represent Category A, I would like a function that filters my data to only those 5 IPs.

  5. Hi is there a way to filter for both horizontal and vertical criteria at the same time ? For example if i wanted to filter the data for first and A:

    2021 2021 2021 2022 2022 2022
    First Mid Last First Mid Last
    A
    B
    A
    A
    B
    B
    A

    1. Hi! Use the FILTER function to get the required values according to the criteria in cells N1 and N2. Combine the resulting arrays using the VSTACK function and HSTACK function. Read more: Combine ranges and arrays in Excel: VSTACK & HSTACK functions. If I understand your task correctly, try the following formula:

      =VSTACK(HSTACK("",FILTER(A2:G2,A2:G2=N1)), FILTER(HSTACK(A1:A14, FILTER(A1:G14,A2:G2=N1)), A1:A14=N2))

      1. So i actually don't need to combine two ranges together. I basically need to filter on one range but filter for the year (horizontal criteria) and then for A (vertical) criteria which seems to be causing me problems. I can filter the array for multiple horizontal criteria or multiple vertical criteria but both at the same time is returning a #VALUES error.

        1. I wrote the formula based on the description provided in the second comment. I don't see your actual data and can't see what the problem is. Your explanation is not very clear.

  6. Hi Team, can filter function be used with Len. I am working on extracting numbers from a column that is equal to 4.Hope this makes sense.Thanks

    E.g , as an example I want to filter on below and from col B only get length which is = to 4. In this Case , Project - 4444 , 1234 , 8924

    Col A Col B
    Project 55615
    Project 4444
    Project 1234
    BC Code 8924

  7. Hi,

    I've used this formula: =INDEX(SORT(FILTER(FILTER(Sales;(Sales[Date]=30/06/2023)*(Sales[Average Days to Pay]>0));{0,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,0,0,0,0,0,0});3;-1);{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20};{1,2,3,4})

    to sort and filter a lot of data into a top 25 result sorted by column C (amount):

    A B C D
    3009 216232 400,217.43 EUR
    3009 204712 399,277.17 EUR
    3009 211030 384,253.73 EUR
    3009 211030 364,468.50 EUR
    3009 211030 363,289.23 EUR
    3009 211030 363,289.23 EUR
    3009 211030 322,569.79 EUR
    3009 200227 301,204.72 EUR
    3009 211030 289,376.29 EUR
    3009 200227 274,188.95 EUR
    3009 200227 271,246.72 EUR
    3009 200227 268,498.22 EUR
    3009 200227 259,159.89 EUR
    3009 201135 233,208.72 EUR
    3009 200227 180,504.10 EUR
    3009 200227 179,657.16 EUR
    3009 208294 168,588.51 EUR
    3009 200227 162,729.76 EUR
    3009 257345 148,199.86 EUR
    3009 247673 148,164.74 EUR

    Now I need that the result shows the total sum of the amounts in column C where the criteria is the customer number in column B only - not individual items. Like this:

    A B C D
    3009 211030 2,087,246.77 EUR
    3009 200227 1,897,189.52 EUR
    3009 216232 400,217.43 EUR
    3009 204712 399,277.17 EUR
    3009 201135 233,208.72 EUR
    3009 208294 168,588.51 EUR
    3009 257345 148,199.86 EUR
    3009 247673 148,164.74 EUR

    Any help please? How the formula should be? Thanks

  8. How do I filter, based on a values in a column, which is dynamic. Example, Column headers are country names, and I need to filter data based on the country name that is entered in cell B1?

  9. How to find answer when the criteria is in range.

  10. Referring to the above "Filter cells containing specific text" section where you filter last names for players which contain the letters "han".

    If you removed the letters "han" from cell G2 and leave the cell blank (ready as a default), would all Last Names be displayed or would nothing be displayed?

    1. Hi! You can download the example file at the end of the article and check the result. All values will be shown.

  11. I find the "Filter cells containing specific text" part very useful (using FILTER, ISNUMBER and SEARCH). I also know that XLOOKUP can be used to find a record using a partial value.

    But I find that searching an array where there are multiple occurrences of the value the XLOOKUP method gives me only one result, whereas FILTER-ISNUMBER-SEARCH method gives me results showing all occurrences.

    Is it possible to use XLOOKUP to return all occurrences of the value, not just the first occurrence?

    Thank you.

    1. Hi XLOOKUP function returns only one value. For returning multiple values, there are other functions that you have correctly named.

  12. As a frequent visitor to this site, especially after we now have many new O365 functions, I miss the 'use case' of concatenating Filter(ed) array results.

    My own solution, is this one starting by combining input data before FILTERING:
    =SORT(VSTACK(Table1[[Column1]:[ColumnX]];Table2[[Column 4]:[Column X+4]]))
    which just mean:
    Concatenate equally sized (by same number of columns) two arrrays.
    Then Filter the resulting array.
    You can concatenate many arrays, just keep the number of columns the same.

  13. Hi all,

    Can I get excel to give me just the sum of all customer invoices amounts when using a filter function?

    In my example below, I have invoices amounts in column C, customer name in column A and Division in Column B. I use the formula:
    =FILTER($C$5:$E$24;$D$5:$D$24="Apples") to get only the division "Apples". And it gives me every single invoice amount filtered only by Apples Division:
    Customer A Apples 345,45
    Customer A Apples 25547,84
    Customer A Apples 72484,25
    Customer A Apples 110138,91
    Customer A Apples 44126,78
    Customer A Apples 41589,27
    Customer B Apples 5598,9
    Customer B Apples 3345,12
    Customer B Apples 3467,4
    Customer E Apples 67144,25
    Customer E Apples 94669,85

    But I need the formula to give me only the sum by customer, like this:
    Customer A Apples 294232,5
    Customer B Apples 12411,42
    Customer E Apples 161814,1

    How can I enhance the formula to have that? Thank you all for the support.

    Original table:
    Customer Name Division Amoun in EUR
    Customer A Apples 345,45
    Customer A Apples 25547,84
    Customer A Apples 72484,25
    Customer A Apples 110138,91
    Customer A Apples 44126,78
    Customer A Apples 41589,27
    Customer B Apples 5598,9
    Customer B Apples 3345,12
    Customer B Apples 3467,4
    Customer C Oranges 21417,38
    Customer D Oranges 18067,22
    Customer D Oranges 4841,43
    Customer E Apples 67144,25
    Customer E Apples 94669,85
    Customer F Oranges 21559,38
    Customer F Oranges 23064,48
    Customer F Oranges 22759,69
    Customer G Oranges 17558,31
    Customer G Oranges 12219,43
    Customer G Oranges 39779,94

  14. I have a workbook where I use the FILTER function in order to make a filter from 16.000 entries from a worksheet to another one, by some criteria and It work just fine on my version (365).

    My problem is that the other guys from my department have Excel older versions (previous than 2021).
    When they open the file in their computers, immediately gives a error (#name) on the destiny filter worksheet.

    I'm trying to manage some VBA Code in order to block calculation on the destiny worksheet (where the FILTER function is set) if the file is open is Excel versions previous than 2021, but so far I was not able to do it.

    I appreciate any help that you can give to me.

      1. Hi
        Thanks for your reply.

        I canno't implement your solution because the destiny worksheet is shared with other department and they canno't have access to all the entries.
        It could be protected, preventing them do change the criterion, but then would not refresh the pivot table.

        Still working on the solution :)

  15. Hello,
    Why my filter formula stop working ?
    I use this formula:
    =FILTER(FILTER(Log!A2:P200,(Log!A2:A200"completed")*(Log!C2:C200="yes"),"Nothing Found"),{0,0,0,1,1,1,1,0,0,1,0,0,0,0,0,1})
    one is working fine, I get everything I am filtering from my Log tab, next day is no working anymore.
    It is either only filtering the first 4 rows or none at all.
    I have to redo the formula again, over and over.
    Anyone know why this happens and how to fix it?

    1. (Log!A2:A200"completed") as your first logical argument should probably be 200="

    2. Hi!
      I can't check the formula that contains unique references to your workbook worksheets. I don't have your data.

  16. Thanks for this review of the Filter Formula options.
    I am having an issue with a worksheet that has a filter and I also want dynamic print area. looking for suggestions

    I am using a filter within a template on a spreadsheet to populate information into the first 5 columns of the spreadsheet which is 15 columns wide. The data result varies depending on the what area is selected in the filter drop down, the filter ranges from 3 - 250 lines of data.
    the first 7 rows are header, the filter formula is in A8, the filter drop down is in A1. The filter is working great.

    problem is dynamic print

    the spread sheet is 15 columns wide.
    data fills in the first 5 columns (row range 3 - 250)

    my dynamic print (DynaPrint) formula goes like this: =OFFSET('Print WS List'!$A$1,0,0,COUNTA('Print WS List'!$A:$A),15)

    what happens when I update the print_area for that sheet to read =DynaPrint, it does work for my first filtered section, then maintains that print area going forward for all new filters instead of changing based on numberof lines with data, or it bumps the DynaPrint out and reverts back to the original selected print_area for that sheet.

  17. I have 2 Doubts while using filter function:

    So the problem statement is like:

    I hv 1000+ no. of combination ,

    EXAP: A B 10
    B C 20
    BA 10
    CA 05
    CB 15

    In this partiular what i need is
    Step 1: Sort Column C such that i get the min row at first sequence,
    Step 2: Keeping Row 1 as in example case CA 05
    filter out all value C from colum A and A value from Column B , whatever combination they have with any other values E,F, G etc.
    Step 3: repeat the same process , Sort find min and get the best combination.

    Any one please help m to solve this in google sheet.
    Thanks in advance.

  18. I have have column with in multy text value which in one categories example
    RAM
    RAM
    RAM
    SHYAM
    SHYAM
    SHYAM
    HARI
    HARI
    HARI
    LARA
    LARA
    LUSHI

    I Want to filter the one type text from all category at other new column - i.e.-
    RAM
    SHYAM
    HARI
    LARA
    LUSHI
    so please help me

      1. Se puede utilizando la funcion de Filter?

    2. Please ans. me help me

  19. Thanks for this article, very useful.
    I've been doing spreadsheets from the days of Framework, Lotus 123, Lotus Symphony and there is always something new to learn.
    I am going through the tax affairs of a friend and have arranged all the income from investments in a table.
    Using FILTER has made it easy to extract all sources of income in a given tax year (UK).
    The use of "*" and "+" had me stumped for a while as I am much more used to using them as mathematical operators, or the AND and OR functions.
    As you probably know, there are quite a few articles on Excel functions, but this was the most informative.
    Microsoft obviously publish help on functions, but the real downside is that their examples don't seem to have anything you can copy or download which makes them of limited use.

  20. Good day

    I have two sheets. One is for sales view, the other is for my view. The sales sheet has limited viewing rights and I use the =FILTER() function to pull through the information from my sheet to theirs. I use the filter function because the data (quote number) pulling though is either marked as "On Hold", "Released" or "Partially Released". I tried INDEX(MATCH) but it only pulls through the first quote where it encounters an on hold status and nothing further.

    FILTER works perfectly for this as when I update the status on my worksheet it automatically updates on the sales sheet, though the problem now comes in that on the same row they need to comment on the quote status (why on hold).

    Say the quote X was on A2 and their comment was on B2
    and quote Y was on A3 and their comment was on B3

    as soon as I mark quote X on my sheet as RELEASED it removes it from A2 (as the sales sheet only showes quotes on hold), moves quote Y up from A3 to A2 but, this is the problem that I need help with, the comment for quote X does not also dissapear. It stays in B2 because it is not linked to my sheet.

    So after a while updating the quote's status none of the quotes and the comments match up.

    When I tried formatting as a table I get #SPILL errors.

    Is there a way to fix this or does anyone have another formula that I can maybe try?

    1. Hi!
      Excel is not designed to work with databases. Therefore, no changes in the filtered worksheet can be saved to the main worksheet.

  21. This is a very useful Guide and I am comfortable using the FILTER formulae to get what I need most occasions. However, I am always stuck when trying to do the following:

    I have one table with 5 columns. Col. A is used as the array to filter. Cols. D and E are the criteria to be used. The user chooses from the (data validation) list to enter the matching criteria in two separate cells. This works just fine when the user does enter BOTH criteria values

    =FILTER(Table1[Project Name], (Table1[Priority]=L2)*(Table1[Progress Indicator]=N2), "None")

    I can enter the two criteria for example: Priority =High Progress =Delay

    But what if I just put in a criteria to one of those cells and leave the other blank. I want the filtered list to return the filtered results that match that one criteria and ignore the cell value that I have not entered a match value.

    I want to get some results if I choose for example: Priority = [value not chosen from list] Progress =Delay

    This returns "None" (i.e. FALSE) when really I want it to return all filtered results where Col. E = Delay.

    I have tried numerous IF formula and nested the FILTER formula but nothing works. Please note that I do not have an "ALL" value either in any columns or in the data validation lists - so effectively I am wanting the formulae to 'USE ALL' if either the criteria cells have no values entered.

    1. Hi!
      Use the nested IF function to choose which filter to use. For example, the filter conditions in F2 and F3:

      =IF(F2&F3="","", IF(ISBLANK(F2),FILTER(A2:C13,(C2:C13>=F3)), IF(ISBLANK(F3),FILTER(A2:C13,(B2:B13=F2)), FILTER(A2:C13,(B2:B13=F2)*(C2:C13>=F3)))))

      I hope it’ll be helpful.

  22. 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.

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

  24. 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.

  25. 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 :)

  26. 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.

  27. 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

  28. 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.

  29. 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.

  30. 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

  31. 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.

  32. 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

  33. 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.

  34. 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.

  35. 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.

  36. 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.

  37. 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.

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

  39. 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.

  40. 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.

  41. 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.

  42. 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.

  43. 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

  44. 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.

  45. 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

  46. 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.

  47. 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 :)

  48. 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.

  49. 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.

  50. 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!

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 :)