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 6. Total comments: 341

  1. Hello
    Great tutorial.
    one more question: How to filter with the new FILTER-function multiple criteria (AND, OR) with wildcard(s), in a table column
    e.g.
    Table:
    Col A Col B Criteria for search in Language column: Result:
    Row 1 Country Language ma (eg. in cell D1) Germany german
    Row 2 Germany german re (eg. in cell D2) France french
    Row 3 France french isc (eg. in cell D3) Spain spanish
    Row 4 Italy italian
    Row 5 Spain spanish

      1. Thank you very much for your reply. That is correct, however I am locking for a possibility to enter more than one search string as in my example above mentioned (the format of the example was lost when transmitting the post that is why it is not easy to read)

        - The Table is in Col A and B Row 1 to 5 named Countries
        - The headers of the table are named "Country" and "Language"
        - The Search Strings (only part of the words in the table) are in Column D Row 1 to 3 (ma, re, isc - who are in normal search with wildcards "*" or "?")
        - The Filter-Function for the result should be in Cell "F1” (the search should be an “OR” search how would it be with an “AND” search ?

        How to distinguish in the Filter Function between exact match, with Wildcard “*” or with Wildcard “?”

        1. Hello!
          Sorry, it's not quite clear what you are trying to achieve.
          The SEARCH function does not use wildcards. Which formula do you want to use?
          Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the expected result.
          It’ll help me understand it better and find a solution for you.

        2. Hello,
          FILER function for more than one criteria:

          After long trials I got now the answer for filtering more than 1 Criteria in a PowerQuery Table.
          1. I have to define a named range for the search area, then define the FILTER formula
          2. Execute the PowerQuery and put it to the in the FILTER formula defined range.
          With this I am able to run the FILTER function with more than 1 criteria

          Eg.
          For Criteria 1 AND 2
          =FILTER(A2:H656;(ISTZAHL(SUCHEN(L2:L2;Betreff))*ISTZAHL(SUCHEN(L3:L3;Betreff)));"kein Resultat")

          For criteria 1 OR 2
          =FILTER(A2:H656;(ISTZAHL(SUCHEN(L2:L2;Betreff))+ISTZAHL(SUCHEN(L3:L3;Betreff)));"kein Resultat")

          For criteria 1 AND (2 OR 3)
          =FILTER(A2:H656;(ISTZAHL(SUCHEN(L2:L2;Betreff))*ISTZAHL(SUCHEN(L3:L3;Betreff))+ISTZAHL(SUCHEN(L4:L4;Betreff)));"kein Resultat")

          Ps.
          ISTZAHL equals Isnumber
          SUCHEN equals SEARCH
          Betreff is the named search range

          For changing between the 3 Filter options (FILTER function) I wrote a macro which changes the FILTER formula automatically

  2. 1. how to filter based on checkbox that contain cell value from table in different sheet?

    2. how to filter based on drop-down that have VBA code which helps to click multi selections that contain cells value from table in different sheet?

  3. Is there a way to use =FILTER across multiple worksheets in a single formula? I'm using it to pull rows that meet certain criteria, but the reports I have to work with can have many worksheets to sift through.

    An example:
    =FILTER(Sheet1!A6:J1667,(Sheet1!G6:G1667="VariableX")+(Sheet1!G6:G1667="VariableY")+(Sheet1!G6:G1667="VariableZ"),"")

    Currently I am pasting a new =FILTER formula below the lowest returned line:
    =FILTER(Sheet2!A6:J1667,(Sheet1!G6:G1667="VariableX")+(Sheet2!G6:G1667="VariableY")+(Sheet2!G6:G1667="VariableZ"),"")

    And so on for how ever many sheets there happen to be. If I was able to build in [filter Sheet1 then also filter SheetN+1 until they are all filtered], it would save me a good deal of time.

  4. Hi Alexander!
    I am mixing up rows and columns some how but found that Svetlana had replied in an earlier question that I could use to get what I was looking for. Only returning specific columns.
    FILTER(FILTER(A2:C13, B2:B13=F1), J1:L1) :)

  5. It did!! Thank you Alexander!

    Could you see why this works and filters.
    FILTER(Data!A:G;Data!G:G="Covid";"No results")

    While this doesn't.
    FILTER(FILTER(Data!A:G;Data!G:G="Covid";"No results");{1;0;0;1;1})

    I constantly get #VALUE!

    Jen

  6. Great tutorial!

    One thing I don't see solved is as for my case.
    I have cell values that sometimes have data and sometimes they are empty.
    I am using the cells for input to my Filter function.
    A B C D
    1 Month Article Cost Owner
    2 202103 John

    FILTER(array, (range1=criteria1) * (range2=criteria2), "No results")
    FILTER(array, (range1=202103) * (range2=)* (range3=)* (range4=John), "No results")

    In the case I don't have any input criteria as in Column B and Column C above.
    It will send the input filter-out-all-rows-where range 1= 202103, range 2=empty, range 3=empty, range 4=John
    How do I get around that I want to have the possibility to use all criterias but sometimes they are empty.
    I currently have 11 cells that I would like to use for criterias.

    Thank you!

    1. Hello!
      Add additional conditions to check an empty criterion cell.

      FILTER(array, ((range1=criteria1)+(criteria1="")) * ((range2=criteria2) + (criteria2="")), “No results”)

      I hope my advice will help you solve your task.

  7. I am using a Filter list to populate another sheet. Based on the names that appear in this new list, I am writing some static formulas/remarks. The problem I am having is that when the Filter list updates, sometimes the new data/names appear ON TOP of the original list, due to which the static data no longer matches with that which was imported from the Filter List. Is there any way to add the new entries from the filter list at the bottom? Or to tie the static data to the dynamic data?

  8. I have an excel column that contains text in around 1000 cells. In each cell, some text is BOLD, some text is ITALALICS, and some text is in REGULAR fonts. Thus, each cell has all three fonts (bold, italics & regular) in it.

    I want to filter cells that contain the text "because" in bold fonts only.

    Can you please help to write FILTER FORMULA for this.

    Yours sincerely,
    Prakash Dave.

    1. Hello!
      Your formatting does not change the "TEXT" format of the cell. Therefore, using Excel formulas, your task cannot be completed.

  9. Is there any way of using array as criteria in a Filter function?
    For instance, in your SUM, AVERAGE... examples - instead of F1 providing the Group as the criteria, calculate SUM for {"A","B","C"...}. The array could have been created using UNIQUE. And so the the output of the SUM will be an array.
    I have a similar problem, and I'm just getting error messages.

    1. Hello!
      For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

      1. Hi,

        I'm referring to the section in this webpage titled: "Filter and calculate (Sum, Average, Min, Max, etc.)"
        I want to have something like the following formula:
        =SUM(FILTER(C2:C13,B2:B13=UNIQUE(B2:B13),0))

        Source data is as per the section in the webpage.
        My aim is to get results that show:
        Group, Wins
        A, 8
        B, 11
        C, 5

        ...and for it to be generated dynamically without knowing beforehand how many groups there are in the source data.
        I get #N/A when I try this.

        1. Hello!
          The FILTER function cannot compare two arrays. You can use these formulas:
          In cell E4:

          =IF(SORT(UNIQUE(B2:B100),1,1)=0,"",SORT(UNIQUE(B2:B100),1,1))

          In cell F4:

          =IF(E4<>"",SUM(FILTER(C2:C100,B2:B1100=E4, 0)),"")

          Copy this formula down along the column F.
          I hope my advice will help you solve your task.

          1. Thanks for that, Alexander. It is useful to know what FILTER *can't* do.
            Is there anywhere that clearly documents the *details* of the new(ish) array and dynamic array functionality? It seems to be a lot for trial and error and searching various blogs.
            I'm looking for something that better explains the interfaces - i.e. where you can and can't use them.

            By the way, there's a slight typo in your formula for cell F4 it should read:
            =IF(E4"",SUM(FILTER(C2:C100,B2:B100=E4, 0)),"")

  10. Hello

    I was wondering if there is any way to automatically insert rows if the filtered data requires more space than available. I was thinking about a dynamic page break which increase and decrease with the data required to be displayed.

    Please advise.

    Thanks
    Z

  11. Hello,

    I am not sure if the "filter" function is the most appropriate, I think I would have to use a combination of functions for my case. So, my dataset looks like the one below.

    Zone 1 2 3

    a 13 12 75

    b 98 0 2

    c 0 100 0

    I need to remove the "0" values and for zone 1 create as many rows as values I have. So, the final format should look like the following,

    Zone

    1 13 a

    1 98 b

    2 12 a

    2 100 c

    3 75 a

    3 2 b

    Could you please suggest me how to do that?

  12. Can the input range to a listbox form control be the spill range of =UNIQUE(....)?

    I want the input range to a list box form control to be all the DIFFERENTsales reps in column A without duplicates.

    1. Hello!
      Extract unique data to any empty column. Use this instruction.
      Then, using the formula, get a list without empty cells:

      =OFFSET($D$1,,,COUNTA($D$1:$D$200))

      Copy the entire formula from the formula bar.
      Select cell, which is where you want the dropdown to be shown.
      Go to Data > Data Validation.
      Paste the formula as the Source.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  13. I'm trying to use a range from another sheet for the array and criteria. It looks like this:

    =UNIQUE(FILTER('Sheet1'!A2:B220,('Sheet1'!E2:E219 "")))

    I'm getting a #Value error. Does referencing other sheets just not work?

    1. I actually figured out the problem and have it working now. But I have a new question. Is there a way to just have it check every sheet in the workbook? Here is my formula now (each sheet is a date):

      =UNIQUE(FILTER('14-01-21'!A2:B219,('14-01-21'!E2:E219"") + ('18-01-21'!E2:E219"") + ('20-01-21'!E2:E219"") + ('21-01-21'!E2:E219"") + ('25-01-21'!E2:E219"") + ('27-01-21'!E2:E219"") + ('28-01-21'!E2:E219"") + ('01-02-21'!E2:E219"") + ('02-02-21'!E2:E219"") + ('04-02-21'!E2:E219"") + ('08-02-21'!E2:E219"") + ('11-02-21'!E2:E219"")))

      As you can see, this is really messy. The other problem is, new sheets will be added as time goes on. That's why I'm wondering if there's a way to have it check E2:E219 in every sheet rather than having add each sheet individually.

    2. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. But the FILTER function was used with an error. Please check out the following article on our blog, it’ll be sure to help you with your task: Get a list of unique values based on criteria.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  14. Thank you for this great article. I just wanted to ask if it is possible to filter using multiple columns in the expression, i.e. =FILTER(A2:C10,ISNUMBER(SEARCH(F2,B2:C10)),"No Results").

    You see I have 3 columns and I want to display all rows that contain the Keyword in either Columns B or C.

    Thanks.

  15. Hi thanks for detailed explanations and enormous work you guys put to create this resource! I have a quick question:
    For instance I have a list of t-shirts. I want to filter t-shirts by color and price ("blue" and "above 5 dollars"). However, I at the same filter I want also give my users an option to filter ALL colors of t-shirts above 5 dollars. What could be the solution? I tried to use IF statements or AND/OR in my formula, but I could not succeed maybe I am doing something wrong. Thanks for your answers!

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.

      =FILTER(A2:C7,B2:B7="blue")

      =FILTER(A2:C7,(B2:B7="blue")*(C2:C7>5))

      =FILTER(A2:C7,C2:C7>5)

      A- name, B - color, C - price

      1. Thanks for your answer Alexander! Unfortunately, your solution requires 3 separate formulas. However, I want to give an option to filter "all colors" as well as individual colors in the same formula.

        =FILTER(A2:C7,(B2:B7="{blue, red, yellow, all colors}")*(C2:C7>5)).

        I want to give a user something like drop down list where he/she can select color from the list "blue", "red", "yellow" as well as "all colors". I can do individuals colors, but I can not do "all colors" option. I also tried to use "*" symbol, but FILTER function does not support wildcards.

        I would appreciate your comment/solution to this problem! Thanks!

          1. Hi Alexander,

            Thanks for your comments and help to master excel. I was able to figure this problem out using IF statements together with FILTER function. Just in case anybody came across with a similar situation below how I solved the problem:

            Step 1.: I created a table with color and prices for T-shirts (i.e. I3"BLUE", J3"12"; I4"RED",J4 "10"; I5"GREEN", J5"5" AND I6"ALL COLORS", J6"0")

            Step 2.: I created a two data input table 1 for Price (B1) and one for Color where you can select individual colors and "ALL COLORS" from the drop down list (I used Data Validation- List).

            Step 3. I created a below formula in cell A4:

            =IF(B2"ALL COLORS",FILTER(I3:J6,(J3:J6>B1)*(I3:I6=B2)),FILTER(I3:J6,(J3:J6>B1)))

            Basically the logic of formula goes as the following:

            If color is not selected as "ALL COLORS", then filter table based on Price and Color, else filter based on Price only.

            Now I can filter T-shirts based on individual colors as well as price only. I know it might sound simple but it took me few days to research and think about it.

            I would be happy if this could help to save someone's time! Have a great day! :)

  16. Hi,

    excellent article, thank you.

    I have multiple data points for several individuals re-occuring on different dates. I am able to filter out the specific people I want based on certain criteria, but can I also get an average of their score for each day.

    Thanks,
    Paul

  17. Hi, I love all the examples! Do you know if FILTER can be used directly in data validation source?

    That is, I would like to have a drop down list with items depending on selection in another dropdown list.
    I tried this formula in cell AB3:
    =FILTER(B1list;$AA$3=BtoB1;"")
    and it works nicely in a cell, but when I try to use it in data validation --> list --> source it says 'The Source currently evaluates to an error".
    (AA3 is where the user is supposed to select the first value and I would like them to only see relevant items from the list BtoB1 list in cell AB3)

    Cheers,
    Kamil

  18. Hello, excellent article, thanks!

    Is there any way to limit the number of rows a FILTER function returns? For exmaple, the function returns 10 rows but I have only space for 5 (and I cannot delete the data below that)... is this feasible?

    Thanks!

  19. Hi,
    Probably an easy question:

    If we look at the example: Basic Excel FILTER formula.

    I would like to have A AND B if F1 is empty, is that possible?

    Thanks in advance.

  20. Great post! This is so powerful and I learned so much!!

    Is there a way to Filter Non-Adjacent Columns and define the Array Constant using a formula (or in some way dynamically)?

    Here's what I mean: I would like the {1,0,1} to change to {1,1,0} based on other criteria in my workbook.

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

    Can change to:

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

    Is this possible?
    Thanks again for this great post!!!!

    1. Hi!

      It' difficult to say anything certain without knowing exactly what kind of criteria you have in your workbook. Anyway, I believe the simplest approach that you can try is nested IFs.

      For example, if K1=1, return {1,0,1}, if K1=2, then return {1,1,0}:

      =IF(K1=1, FILTER(FILTER(A2:C13, B2:B13=F1), {1,0,1}), IF(K1=2, FILTER(FILTER(A2:C13,B2:B13=F1), {1,1,0}),""))

      1. Thank you Svetlana. That is not quite what I'm hoping to accomplish. I would like to create the Array Constant Dynamically. So in this example, cells A1, A2, A3 might contain 1's and 0's that would be used to populate the Array Constant. So if A1=1, A2=0, and A3=1, the Array Constant would be {1,0,1}

        In short, I would like to build the {1,0,1} parameter using a formula.

        Is this possible?

        Thank you again! SJ

        1. Oh, that makes things even easier :)

          Instead of an array constant, you can use a regular range reference. For example:

          =FILTER(FILTER(A2:C13, B2:B13=F1), J1:L1)

          Where J1:L1 are the cells containing your 1 and 0 values.

          1. OMG Svetlana you are the absolute best!!!!

            One more question. This works when I use a horizontal range but not when I use a vertical range. Just curious why? And any way I can work around this?

            As an example:
            =FILTER(FILTER(A2:C13, B2:B13=F1), J1:L1)
            works fine

            =FILTER(FILTER(A2:C13, B2:B13=F1), J1:J3)
            does not

            Thank you so much - this has save me countless hours!!! You are like an Excel Angel!!!

            1. Thank you :)

              As for your question, it's because {1,0,1} is a horizonal array, so it requires a compatible horizontal range.

              If your data was organized horizontally in rows (i.e. if you transposed your table), then the FILTER formula would require a vertical array constant like {1;0;1} to return data from specific rows. And you would replace that array constant with a vertical range.

              We have a couple of in-depth tutorials on Excel arrays and traditional CSE array formulas. If you are curious, you can check them out:

              Excel array formulas, functions and constants (among other things, it explains the difference between vertical and horizonal arrays)

              Excel array formula examples for beginners and advanced users

  21. So how do you make it work with VBA.
    With the first example I used =FILTER(A2:C13,B2:B13=F1,"No Results") in excel and it worked as shown.
    Next I created a macro and it FAILED!!!!
    Here is the macro
    Sub test()
    Range("I4") = Filter(Range("A2:C13"), Range("B2:B13") = F1, "No Results")
    End Sub
    Run-time error '13': Type mismatch

  22. Hello, your guides are amazing.
    I have a question: is it possible to connect two filter arrays es.
    FILTER #1:
    a - 1
    b - 2
    c - 3
    FILTER #2:
    d - 4
    e - 5
    Connected Filters:
    a - 1
    b - 2
    c - 3
    d - 4
    e - 5
    Thank you :)

    1. I believe you were looking for something like this:

      ={FILTER(Sheet1!F2,Sheet1!F2:F0);FILTER(Sheet2!F2,Sheet2!F2:F0);FILTER(Sheet3!F2,Sheet3!F2:F0)}

      This will return a filtered list of all 3 sheets.

      Works like a charm in Sheets, I'm still trying to figure out how to make it work in excel.

      1. Thank you for the answer.
        I mean another thing:
        I have 2 different starting matrix that I filter with the FILTER function.
        In the way I obtain 2 different filtered matrix.
        FILTERED MATRIX #1
        a - 1
        b - 2
        c - 3
        FILTERED MATRIX #2:
        d - 4
        e - 5
        What I would like to obtain is a new (dynamic) matrix that is the fusion of the two filtered matrix.
        ES. OF THE FINAL MATRIX:
        a - 1
        b - 2
        c - 3
        d - 4
        e - 5
        Without using VBA

          1. Not exactly.
            As I said the starting matrices are two and are different, so the FILTER functions refers to different matrices.
            Here is an exemple:
            =FILTER(A1:B10;A1:A10=5)
            =FILTER(AA1:BB10;AA1:AA10=5)
            As you can see the starting matrices are different (A1:B10 and AA1:BB10)
            What I want is to combine the resulting arrays into a single matrix.
            In other words how can I "merge" two different matrices into one, without using VBA?

            1. Hello!
              Merge an Excel spreadsheet using formulas is not possible in your case. You can learn more about it in this article.
              We have a ready-made solution for your task - Copy Sheets Wizard. 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.
              If there is anything else I can help you with, please let me know.

              1. Thank you Alexander

    3. I mean without VBA

  23. Is it possible to use the Filter Function to filter on format, in this specific case, color? I'm trying to avoid VBA for this specific workbook.
    Thanks

  24. I'm trying to enter this function into an xlsm sheet whic has other macros. However, it is returning error #Value... any help would be really appreciated

    Im unable to upload my file or could have shared the same as well

    1. Hello!
      Check the data in the cells you are referencing. Do they match the value in the filter?
      Please specify, what formula you used. Give an example of the source data.
      It’ll help me understand your request better and find a solution for you.
      See also this section above.

  25. Dear Sir,how to filter data ,(Like Company Record of Daily Expanse to Employees,i want to select only one date and the all associated data of that day will show by choosing date)

  26. Regarding using aggregate functions wrapped around filter results ... set {is empty] to zero works fine for most aggregate functions such as SUM, but is problematic for COUNT functions. The intention behind wrapping the filter function with COUNT is to count rows. If no rows exist matching the filter criteria, then , I want the COUNT function to return zero. Maybe I am missing something, but this is a lot harder to do than I thought. Maybe I need to abandon the FILTER and UNIQUE function to use other methods. Any suggestions? Thanks

    1. Hello!
      You have not listed the formulas that are causing the problem. But I think using function IFERROR will help replace the error message with zero.
      If there is anything else I can help you with, please let me know.

  27. How do i create a filter on an ever changing range (sometimes 100 rows, sometimes 250, or even 10) - do i have to preset the filter to cover x number of rows, or can i create dynamic range

      1. Hi Alexander,

        Can we filter by connecting to a dynamic range via a pivot table?

  28. With is formula I pull all rows for the week and corresponding times. I sort on the week. Is it possible to add a blank line to separate each day of the week? My spreadsheet has 85 columns, I show column 1 (date) and column 2 (key time). I also need sort both the Date and Key Time. Thank you for any assistance.

    =FILTER(SORT(FILTER(TDaily[[Date]:[Key Time]], (TDaily[Date]>=I10) * (TDaily[Date]<=I11)), 1, 1), {1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,82})

    1. Hello!
      The FILTER function returns an array of values. Therefore, it cannot return any empty lines. If you create a pivot table from this data, then different formatting methods are possible there.

  29. Hi Lady,

    Is it posible to filter if a column matches any of a list of values.

    Example:

    List of criterias
    A1: 1
    A2: 2
    A3: 3
    A4: 4

    The data i want to filter:
    B1: 1
    B2: 4
    B3: 3
    B4: 6
    B5: 7
    B6: 2

    =FILTER(dataToFilter, dataToFilter=listOfCriteria, "No results")

    Result:
    1
    4
    3
    2

    is there a way to do this?

    Thanks in advance!

      1. Hello 👋 , i install office 2021 but filter function is not available in the functions, can you help me? Please

  30. Hi,

    In your examples, how do you return just Name and Wins for example

  31. Hello!

    One doubt:

    How do I Add a last row with column sum, using filter? Could I do that?

    1. Hi Lincoln,

      This can be done by using the SUM function with a spill range reference (# symbol).

      Assuming the FILTER formula is in E4, the SUM formula will be as simple as this:
      =SUM(E4#)

      Just make sure there are enough empty rows between the FILTER and SUM formula cells. If FILTER does not have the required number of rows to fill with the results, a #SPILL error will occur.

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