Which Of The Following Is Not A Valid Criteria In A Filter?

152 comments to "Excel FILTER function with formula examples"

Enrique Lopez says:

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

For case if I only identify a value in Groups of A and leave Wins blank it shows all the A results, but if I add a value to wins so it narrows information technology downward. And if I remove the Group value and simply have wins it shows values of all wins to a higher place "2" ignoring the Group value since information technology's bare.

I cant seem to figure this out.
Siddharth says:

I have values in columns A to Z
Now, I desire to go values from column A, B, C, Chiliad, Northward, and P if in that location is a "mistake" value in column Z. In column Z in that location are unlike values such equally error, set, not fix and so on. I need values from in a higher place mentioned columns if there is a "error" value in cavalcade Z. Please assist me out.
- Howdy!
  The FILTER part applies to only i range. So utilize information technology multiple times.
  
  =FILTER(A2:C7,Z2:Z7="fault")
  
  Please re-check the article higher up since it covers your case.
Jean says:

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 larn how the Filter formula works, I employ it for everything, but I know the older versions of Excel didn't take this option, and with this article I could learn all that I needed about this amazing office in Excel.

Thanks for this astonishing job!
Jeff Small says:

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

If we use the table of data you have used in this article (Proper noun, Grouping, and Wins), what I want to exercise is filter out Group B and too decide 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 gear up my filter function to filter for Grouping B AND the largest value, I become an error as my filter is finding the largest number in the Wins column (which is iv from both Groups A and C) and it does not correspond to Group B, therefore, the error.
I desire to keep the filter dynamic and thus wanted to utilise the filter part, but I can't seem to make this work even though it sounds simple. Essentially, I need the filter function to wait just at the number of wins in Group B and so make up one's mind 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.
- How-do-you-do!
  You can use the MAXIFS function to add together another condition to the filter. Pay attention as well 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 promise my advice will help you solve your task.
Jesling Saw says:

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 lot help me on this. Thank you.

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

Hello,
After creating a filtered table (using this filter function) I demand to extract the resulting tabular array 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.
- Hello!
  Highlight your filter table, copy it and use Paste Special - Values. Yous will just insert values without formulas.
Marc Thou says:

Hello,

I've been trying to filter a data gear up that will render results based on ii user input options (instance: InpA and InpB). I tried using the information from the "Multiple AND/OR" article, but the formula that I apply seems to exclude the results that comprise the inputs.

"InpA" data is plant in the get-go 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 office to include each cavalcade (example table is below). Any insight would be greatly appreciated! Cheers!

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-ane ST-two ST-three 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
- Hi!
  Information technology is very hard to empathize a formula that contains unique references to your workbook worksheets. Hence, I cannot check its piece of work.
  Here'south an example of a formula that works. I assume it is similar to your weather condition.
  
  =FILTER(A2:D13, (B2:B13=F2)*((C2:C13=1)+(D2:D13=2)), "No results")
Irshad says:

Hello Everyone,

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

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

I accept tried this formula with IF function but I go an fault.
- Hi!
  I can't guess which formula doesn't work for you. But this formula works:
  
  =IF(A1="ABC",FILTER('1'!A2:C13, 'one'!B2:B13='one'!F1, "No results"),FILTER('ii'!B1:M3,'two'!B2:M2= 'two'!B6, "No results"))
Ali says:

Howdy Everyone,

I take an Excel file for daily reports of a projection, each day has a sheet, named the date of that day, so we add a sheet every day.
in the "summary" sheet I desire to take 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 prison cell of that sheet into this new table cell
Can y'all delight permit me know if you know how to do it?
Thank you
Danielle says:

Hi,

I am using the formula FILTER(IF(Data!A4:N143="","",Data!A4:N143),Data!F4:F143=A1) this is crawly and really working well. I exercise take one issue though and that is that I am getting the #VALUE! mistake on cells where the information 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 in that location a way that I can make it pull through all the text in the jail cell?

Cheers
John Sauve says:

Can the array exist from another workbook on my server?
The workbook on the server updates every xxx minutes with fresh data, I would similar to give my employee a lookup spreadsheet that extracts from the file on the server.
Mark says:

Hi,

For my work, i need to extract and filter data from 2 columns, but count how many times no data was added.
Eg:
(Column J two-300) (Column L 2-300)
Acknowledged by Context
Baby-sit.1 Information added
Guard.2 (Blank)
Guard.one (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 utilize a pivot table equally i need to be able to extract the information for the day into a separate tabular array, then export this information to a split tab within the same spreadsheet.

Do you have whatever ideas on how this can work??
- Hello!
  To excerpt rows with empty cells in a column, you lot tin can use a formula similar this:
  
  =FILTER(A1:C100,ISBLANK(B1:B100))
  
  I promise I answered your question. If something is withal unclear, please feel free to ask.
  - Marker says:
    
    Hullo Alexander,
    
    The issue is that non simply practice i need to filter to locate the bare spaces, I also need to filter to match the guard who has left the expanse blank (not added context) and count them to place into another table.
    
    and so the finish goal is once i enter the raw data, with using the formulas, to fill in a graph which will exist below:
    Baby-sit Proper name - NO CONTEXT ADDED
    Guard.1 - (amount of no context added)
    
    Regards
    - Hello!
      If I understood correctly, your summary table has 2 columns. Y'all cannot create it with i formula. With the formula I gave you, you lot tin fill in the first column.
      
      =FILTER(A1:A100,ISBLANK(B1:B100))
      
      To make full the second cavalcade, the COUNTIFS office can be used:
      
      =COUNTIFS(A1:A100,D1,B1:B100,"")
      
      Hope this is what you need.
Tim says:

Howdy and thank you for a not bad 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:

Cavalcade A Column B Column C Cavalcade D
Input i User 1 Information ane
Input two User two Data 1
Input 1 User 3 Data one

So here is what I want the outcome to exist. If column a equals 1 and Column C equals Data one or Cavalcade D equals Data 1then list Cavalcade B in Column E. And then it would expect like this on Column Due east (the columns higher up are in a table labeled roster)

Information ane
____________
User ane
User three

Here is my formula I have but I go along getting all three users

FILTER(Roster[[Column B]:[Column B]],(ISNUMBER(SEARCH("Input 1*",Roster[[Column A]:[Cavalcade A]])))*(ISNUMBER(SEARCH("Data ane",Roster[[Cavalcade C]:[Cavalcade C]])))+(ISNUMBER(SEARCH("Information 1",Roster[[Column D]:[Column D]]))),"No Users")
- Hi!
  I cannot test your formula as it contains references to your data tables. Hither's a sample formula for you:
  
  =FILTER(B1:B3,(A1:A3="Input 1"*((C1:C3="Data1")+(D1:D3="Data1"))))
  
  There are three conditions used hither.
Adam Desmond says:

I'm trying to use drop down lists to filter a table on two variables (teacher and menstruation) and then 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 "one" matching the data in prison cell B1.

What I want to do is have a 2d prison cell, C1, control the cavalcade name "ane" merely I can't figure out the right way to format it.
- Hello!
  If I understood the problem correctly, you lot can use different FILTER formulas for unlike C1 values using the IF function.
  For case:
  
  =IF(C1=1,FILTER(A2:C13, B2:B13=F1, "No results"),IF(C1=two,FILTER(A2:C13, C2:C13=F1, "No results"),"0"))
Steve Cosgrove says:

I accept a column of text values and wish to filter based on the length of each value. I simply 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 offset column, but information technology would be more than elegant to practise this without having to create additional meta-information.
- Hello!
  To fix a filter conditionally, y'all can utilise the recommendations from this commodity.
  The formula might be something similar this:
  
  =FILTER(A1:A10,LEN(A1:A10)<v)
Robert says:

Is there anyway for the "include" office of the formula to exist based on multiple criteria in the same column? Using the showtime example in your article, could I make the filter array return all of the "b" and "c" into one output?
- - Robert says:
    
    Oaky - sorry - I read that again carefully. I had missed the "Or Logic" department initially. Thanks so much - that scenario fits perfectly!
Alice says:

Howdy!

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

Thanks.
- Hello!
  The FILTER part works merely with 1 data area. Perhaps this article will be useful for yous: Consolidate in Excel: Merge multiple sheets into one.
Arena says:

I'thou trying to filter the data but be able to edit the data that'south filtered. With dynamic filtering information technology's a preview of the data in the table. How exercise I edit the information that's filtered with dynamic filter?
- Hello!
  Y'all need to edit the data in the cells in which they are written.
Yash says:

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

In my code I accept used something similar this to render data consisting of limited columns (columns 1 and three in code with {1\iii}) but with unlimited rows.

How and where should my condition for limiting the number of rows go in the post-obit code

=FILTER(Index(physician!D9:F19;SEQUENCE(ROWS(doctor!D9:F19));{1\three});(mD!X9:X19=TRUE)).

I tried adding the rows before the {ane\three}, but information technology did not work.

Since the code I have has structure FILTER(INDEX...) different from the example here Alphabetize(FILTER...), I am not able to interpret the solution
KC says:

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 case, if I accept something similar this:

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

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

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

The data I'thou filtering can have multiple lines within a single jail cell. How can I ready the workbook to autoFit the superlative when the information is pulled in?
Jos says:

Tin yous please assist me to "Filter on blanks". The data set has
Column A has a list of Names
Column B has operation grades 1-5 (of which some will be blank, so if column B is bare return the corresponding Proper noun.

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

0
Bob Smith
0
0
0
0
Jane Evans
- Hello!
  If you want 0 non to be shown in empty cells, use the custom number format
  
  ###
  
  I hope I answered your question. If something is still unclear, please feel free to inquire.
Sai Kothapalli says:

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 tin I added multiple tables that are in various worksheets to the above formula?

Hope someone helps. Thanks
- Hullo!
  The FILTER role 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 manner and check how it works for gratuitous.
  To piece of work with multiple tables on ane canvass might be helpful - "Example ane. Filter multiple columns in ExcelExample 1. Filter multiple columns in Excel". Read if above.
Karl says:

Hello Alexander - thanks for the commodity, I've constitute the FILTER function very useful for parsing data.

I'm trying to apply your does non equal logic to exclude rows of data that contain specific names. For instance: =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.

Whatever help is much appreciated.
- Karl says:
  
  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.
- Hello!
  If I understand your chore correctly, the following formula should work for you:
  
  =FILTER(A1:B30,(A1:A30<>"Pinnacle Inc.")*(A1:A30<>"Smith Inc.")*(A1:A30<>"Alex Inc."))
  
  Hope this is what you demand.
Hugo says:

Hello.

I take a question.
Is possible to use filter to render paradigm/icon?
I have a icon with a link fastened in each row of my tabular array and when I filter the table I would like that the icon also to appear in the result table...

Is it possible with FILTER?

Thank you.
- Hello!
  The filter works with data that is written in a cell. The icon is fastened to the cell. Therefore, the filter does not run into it.
Tim Newton-Howes says:

Not a question, but i but wanted to say that
1) This is excellent (particularly when you match with [0,0,i,1,0,one...} to return specific columns, and
2) Wrapping FILTER in a SORT function is particularly useful in my context.

I can see all sorts of possibilities forreturning dynamic tables based on user-chosen bank check boxes (for example).

At present i just demand to piece of work out how to identify unique strings of text within other strings of text in filtered array! But ill get at that place
- - Tim Newton-Howes says:
    
    Thanks Alexander, i have looked here and cant quite work out exactly the right formula to give me the asnwer i demand. I take left a question on that thread though.
    
    The most bones case of my problem is, i have two cells, lets say "i, ii" and "ane, iii". I demand to count the unique strings inside those cells so im counting 1, two, and 3 a single time each.
RENE HADDINGTON says:

Hi,

This formula is amazing!! I accept i problem though, my original data is in a separate workbook. When I showtime added the formula (to my new workbook) it filtered perfectly, but over time the original data changes, more than rows gets added all the time, just the filter formula doesn't update to include those new rows, even when they run across all the criteria. I have set the assortment (in the formula) to exist really big, to allow space for the new rows. Is this because its in a different workbook? Or could it exist how I have saved it? Or is it considering one time information technology has been saved it doesnt update anymore?

Thank you lot
Rene
- Hello!
  In society to update the information, all the necessary files must be opened in Excel. Or click the "Refresh All" button in the Data menu
Matt says:

Hullo,

Hope you are all well.

I am a bit stuck. I am using Index and FILTER to list job numbers per site. My problem is that some sites have more job numbers raised against them than others, so to try and not have the spreadsheet huge and and so having to roll down loads, id like to accept 2 columns for the job numbers.

I site may have xiv chore numbers, and I have express it to show just x, only I would like the remaining four to and so overflow onto the cavalcade next to it, then all job numbers are still visible

Is this possible?

Many thanks in advance
- How-do-you-do!
  I kindly ask you to have a closer await at the following paragraph of the article to a higher place - How to limit the number of rows returned by FILTER function. It contains answers to your question.
  If something is however unclear, please feel free to ask.
  - Matt says:
    
    How-do-you-do Alexander,
    
    I had already read that, I accept limited how many jobs numbers it shows, but I am in need of the residuum be shown in the column next to it.
    
    Instance:
    I accept 20 cells, over 2 columns, I have 14 job numbers, ten of the job numbers are shown in the 1st column, the remaining 4 I want to prove in the 2nd column. So where all the chore numbers don't fit in the 1st cavalcade, they overflow into the second column.
    
    Many cheers in advance
    - Hi!
      You can specify numbers from 10 to 20, etc. in the formula. For example:
      
      =IFERROR(Index(FILTER(A2:C13, B2:B13=F1), {10;xi;12;13;fourteen;15;xvi;17;eighteen;19}, {one,ii}), "No result")
SK says:

Thanks for the wonderful guide ,
However , I had a uncertainty. Is there any method that tin can provide me all the outputs (matches) in one bare cell instead of a list ?

To put things into context , I have information in rows A2 to G12345 , I am looking for multiple matches against a Customer ID (having multiple occurrences) . I would therefore not desire another list being shown as the output , instead , want to have all matches in cell J2 and if possible , separated by commas ? . Maybe a combination of formulas could do the fox ?
- Hello!
  To combine FILTER results in one cell, employ the office TEXTJOIN.
  For example:
  
  =TEXTJOIN(",",True,FILTER(A2:A13, B2:B13=F1, ""))
  
  Promise this is what you need.
James Foo says:

Hi, greetings and thanks for those good examples on FILTER.

Hope to clarify, when I selected any cell inside the range of E4:G7 in worksheet "Bones FILTER formula", I noted that the whole range gets a "border" around it. And I think this is not a named range. May I seek for advice what's that about and how to do that? I noted when I selected the whole range of E4:G7, the proper noun box showed E4#. What did that hateful and the part of it?

Capeesh for you guidance and thanks once again.
- Hi James,
  
  A range highlighted with a blue border is chosen a spill range. It is created by Excel automatically when yous use a dynamic assortment formula that returns multiple values like our FILTER formula. A spill range indicates that everything within it is calculated by the formula in the topmost cell.
  
  E4# is a spill range reference that refers to the whole spill range. Unlike a regular range reference, a spill range ref is dynamic and automatically reflects changes in the range size.
  
  All this is office of Dynamic Arrays functionality introduced in Excel 365.
  
  For more information, please run into Excel spill range explained.
  - James Foo says:
    
    Thanks and then much for the update. I shall read through more on the spill range and revert if I need further guidance.
  - James Foo says:
    
    Hi @Svetlana, thanks and so much. I read through the guide and information technology is now working and I clearly understood how information technology works.
Michael says:

Hullo,
Any way to filter out specific columns without using the {one,0,0,0,1,…………} format? Would be helpful if 1 could use a lucifer on column proper noun instead somehow.

Thanks!
- Gary says:
  
  If source information is in a table named tbData to do this I use xmatch( "range of list of columns to match", tbData[#Headers] ). You can so pick which columns to include and they can exist in a different lodge to the columns in the original table. Hope this helps.
sunny says:

How to Excel 365 Filter with indirect assortment of addresses not continuous:
=FILTER(INDIRECT({"$M$ii";"$N$ii";"$O$ii";"$P$2";"$Q$2";"$R$2"}),
INDIRECT({"$M$2";"$North$2";"$O$two";"$P$ii";"$Q$ii";"$R$ii"})""
)

Intermediate Formula Evaluation shows:
=
FILTER(
{"test5";#VALUE!;"test6";"";0;0},
{"test5";#VALUE!;"test6";"";0;0}""
)
Final output shows #VALUE! instead of non-blank cell values.
Thanks Ahead!
Diana says:

I'm going crazy trying to figure out why I don't have access to the FILTER() function in Excel. I have MSFT 360 and have tried using both the "Beta Channel" and "Current Channel (Preview)" and neither update has installed the FILTER() function. I found an sometime article that referenced Microsoft 360 "Pro" but don't run into whatsoever identify to purchase that on the Microsoft website, only "family" or "business organization" editions. AFAICT, there's no departure in functionality on those versions, merely the number of people who can utilise it. Tin can yous please let me know what version I need in order to admission this office or, mayhap, a workaround with a different function?

Thank you!

BTW, my Excel version is 16.50 if that helps
- Howdy,
  If you lot actually have an Function 365 subscription, then the filter office should exist there. You can read about this in more detail here.
  - Diana says:
    
    Thanks for your super fast reply. Yes . . . it'south a caput scratcher for certain . . . I definitely accept a 360 subscription (can log in and verify). Maybe it'southward because I'yard running it on a Mac. Oh well. :(
    - Hullo,
      Unfortunately, I cannot help y'all here. But the FILTER role is as well available on Mac. Wait carefully at the link I gave you. FILTER is currently bachelor to Microsoft 365 subscribers in Current Channel.
Jack says:

How-do-you-do, Fantabulous info on the filter function... saved me many weeks of work!

I wonder if you could assistance me on this:

Am applying a filter on a cost list, to return a set of products based on the blazon of category - and this works fine. But now I would similar to eliminate rows which take become "obsolete" since price has changed. For example I accept this data prepare:

PRODUCT - PRICE - Terminal Price Alter
Java Brand A - $5 - 1/1/2020
Tea Make X - $1 - 5/12/2020
Coffee Brand B - $four - i/5/2021
Coffee Make A - $half dozen - four/30/2021

I would similar my output to IGNORE the first row, dated 1/i/2020 since the price for that brand (Coffee Brand A) has now changed on 4/30/2021.

Is there a way to reach this dynamically? I would demand to include this as part of the FILTER role, since the tabular array higher up would accept been "built" by using a filter on the category called "Tea&Java".

Thank you!
- How-do-you-do!
  Please try the post-obit formula:
  
  =FILTER(A1:C6,(D1:D6="category")*(C1:C6=MAXIFS(C1:C6,A1:A6,A1:A6)))
  
  You can learn more about MAXIFS function in Excel in this article on our weblog.
  - Jack says:
    
    Hi,
    
    I tried the formula and it worked perfect in selecting the right row from the two dates. The trouble I have is that I want all records to be returned, except the "obsolete" one. To use my example again:
    
    The total list is this:
    
    Product - PRICE - Final PRICE CHANGE
    Coffee Brand A - $5 - 1/1/2020
    Tea Make X - $ane - 5/12/2020
    Coffee Brand B - $iv - ane/v/2021
    Coffee Brand A - $6 - iv/30/2021
    
    Your suggested equation returned:
    
    PRODUCT - Cost - Final PRICE CHANGE
    Coffee Make A - $half dozen - 4/30/2021
    
    What I would like to return is this:
    
    Production - PRICE - LAST PRICE Alter
    
    Tea Make X - $1 - 5/12/2020
    Java Brand B - $4 - 1/v/2021
    Coffee Brand A - $6 - 4/30/2021
    
    Can you advise a modification please?
    
    Cheers so much for your help.
    - Jack says:
      
      I recall I institute a workaround: I introduced a column which flags whether that row is "obsolete" or non. So used an "*" in the filter to check against it.
      
      Equation now looks similar this:
      
      =UNIQUE(FILTER(FILTER(T_PRI, (Table2[[#All],[CATEGORY]]=B17)*(Table2[[#All],[OBSOLETE]]="")),{1,1,one,ane,0,i,ane,0,0,0,1,ane}))
    - Hello!
      The formula I sent to you was created based on the description you provided in your first request.
      You wrote: "Am applying a filter on a price list, to return a set of products based on the type of category". If yous need a complete list, and so remove this condition from the filter.
      
      =FILTER(A1:C10,(C1:C10=MAXIFS(C1:C10,A1:A10,A1:A10)))
      
      Hope this is what you need.
Wolfgang says:

Hello
Great tutorial.
one more question: How to filter with the new FILTER-role multiple criteria (AND, OR) with wildcard(southward), in a tabular array column
e.g.
Table:
Col A Col B Criteria for search in Linguistic communication column: Outcome:
Row 1 Country Language ma (eg. in prison cell D1) Federal republic of germany german
Row 2 Frg high german re (eg. in cell D2) France french
Row 3 French republic french isc (eg. in cell D3) Spain castilian
Row 4 Italy italian
Row v Spain castilian
- - Wolfgang says:
    
    Thank you very much for your answer. That is right, however I am locking for a possibility to enter more than 1 search string every bit in my example above mentioned (the format of the case was lost when transmitting the post that is why it is not easy to read)
    
    - The Table is in Col A and B Row i to 5 named Countries
    - The headers of the table are named "State" and "Linguistic communication"
    - The Search Strings (only part of the words in the table) are in Cavalcade D Row i to 3 (ma, re, isc - who are in normal search with wildcards "*" or "?")
    - The Filter-Role for the consequence should be in Prison cell "F1" (the search should be an "OR" search how would it exist with an "AND" search ?
    
    How to distinguish in the Filter Function between exact lucifer, with Wildcard "*" or with Wildcard "?"
    - Wolfgang says:
      
      Hi,
      FILER part for more than ane criteria:
      
      After long trials I got now the answer for filtering more than i Criteria in a PowerQuery Tabular array.
      1. I accept to define a named range for the search surface area, and then define the FILTER formula
      two. Execute the PowerQuery and put information technology to the in the FILTER formula divers range.
      With this I am able to run the FILTER office with more 1 criteria
      
      Eg.
      For Criteria 1 AND ii
      =FILTER(A2:H656;(ISTZAHL(SUCHEN(L2:L2;Betreff))*ISTZAHL(SUCHEN(L3:L3;Betreff)));"kein Resultat")
      
      For criteria ane 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
    - Hello!
      Sorry, it'southward not quite articulate what you are trying to accomplish.
      The SEARCH part does non use wildcards. Which formula do you want to employ?
      Please specify what you lot were trying to discover, what formula you used and what problem or error occurred. Give an example of the expected event.
      It'll help me sympathise it better and discover a solution for you.
michael says:

1. how to filter based on checkbox that incorporate cell value from tabular array in dissimilar sheet?

2. how to filter based on drop-downwardly that have VBA code which helps to click multi selections that contain cells value from table in dissimilar sheet?
Levi says:

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

An instance:
=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 everyman returned line:
=FILTER(Sheet2!A6:J1667,(Sheet1!G6:G1667="VariableX")+(Sheet2!G6:G1667="VariableY")+(Sheet2!G6:G1667="VariableZ"),"")

And so on for how always many sheets at that place happen to be. If I was able to build in [filter Sheet1 then besides filter SheetN+1 until they are all filtered], information technology would save me a proficient deal of fourth dimension.
Jen says:

Hi Alexander!
I am mixing up rows and columns some how but plant that Svetlana had replied in an before question that I could use to become what I was looking for. Merely returning specific columns.
FILTER(FILTER(A2:C13, B2:B13=F1), J1:L1) :)
Jen says:

It did!! Thank you Alexander!

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

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

I constantly get #VALUE!

Jen
- Hello!
  The criteria in the FILTER function must return TRUE or FALSE. Your values {one;0;0;1;1} don't.
  Pay attention to the following paragraph of the article to a higher place - Filter with multiple AND criteria.
  I hope it'll be helpful.
Jen says:

Great tutorial!

One matter I don't see solved is as for my case.
I have cell values that sometimes have information and sometimes they are empty.
I am using the cells for input to my Filter function.
A B C D
1 Calendar 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 whatever input criteria as in Column B and Cavalcade C above.
It volition transport the input filter-out-all-rows-where range 1= 202103, range 2=empty, range three=empty, range four=John
How practice I become around that I want to have the possibility to use all criterias but sometimes they are empty.
I currently accept 11 cells that I would like to employ for criterias.

Cheers!
- Hello!
  Add additional weather condition to check an empty benchmark jail cell.
  
  FILTER(array, ((range1=criteria1)+(criteria1="")) * ((range2=criteria2) + (criteria2="")), "No results")
  
  I hope my advice will aid you lot solve your task.
Anmol says:

I am using a Filter listing to populate another sheet. Based on the names that appear in this new list, I am writing some static formulas/remarks. The trouble I am having is that when the Filter list updates, sometimes the new data/names appear ON Acme 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 mode to add the new entries from the filter list at the bottom? Or to necktie the static data to the dynamic data?
Prakash Dave says:

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

I desire to filter cells that incorporate the text "because" in assuming fonts only.

Can you please assist to write FILTER FORMULA for this.

Yours sincerely,
Prakash Dave.
- Hello!
  Your formatting does not change the "TEXT" format of the cell. Therefore, using Excel formulas, your task cannot exist completed.
David says:

Is at that place any way of using assortment as criteria in a Filter function?
For instance, in your SUM, Boilerplate... examples - instead of F1 providing the Group as the criteria, calculate SUM for {"A","B","C"...}. The assortment could have been created using UNIQUE. And then the the output of the SUM will be an array.
I have a similar problem, and I'k just getting error messages.
- Hello!
  For me to be able to help you amend, please depict your task in more than detail. Please specify what y'all were trying to find, what formula yous used and what problem or error occurred. Give an example of the source data and the expected result.
  It'll help me empathise it better and find a solution for you.
  - David says:
    
    Hi,
    
    I'm referring to the department in this webpage titled: "Filter and summate (Sum, Average, Min, Max, etc.)"
    I desire to have something like the post-obit formula:
    =SUM(FILTER(C2:C13,B2:B13=UNIQUE(B2:B13),0))
    
    Source data is every bit per the department in the webpage.
    My aim is to go results that show:
    Group, Wins
    A, eight
    B, 11
    C, 5
    
    ...and for it to be generated dynamically without knowing beforehand how many groups at that place are in the source data.
    I get #Due north/A when I try this.
    - Howdy!
      The FILTER role cannot compare two arrays. You can utilise these formulas:
      In cell E4:
      
      =IF(SORT(UNIQUE(B2:B100),1,i)=0,"",SORT(UNIQUE(B2:B100),1,1))
      
      In prison cell F4:
      
      =IF(E4<>"",SUM(FILTER(C2:C100,B2:B1100=E4, 0)),"")
      
      Copy this formula down along the column F.
      I hope my communication will help you solve your job.
      - David says:
        
        Thanks for that, Alexander. It is useful to know what FILTER *tin't* practise.
        Is at that place anywhere that clearly documents the *details* of the new(ish) assortment and dynamic assortment functionality? It seems to exist a lot for trial and fault and searching diverse blogs.
        I'm looking for something that better explains the interfaces - i.east. where you can and can't employ them.
        
        Past the way, there's a slight typo in your formula for prison cell F4 it should read:
        =IF(E4"",SUM(FILTER(C2:C100,B2:B100=E4, 0)),"")
Ziyaad says:

Howdy

I was wondering if there is any style to automatically insert rows if the filtered information requires more infinite than available. I was thinking almost a dynamic page interruption which increase and decrease with the data required to be displayed.

Please advise.

Thanks
Z
Eva says:

Hello,

I am not sure if the "filter" function is the about advisable, I recall I would have to use a combination of functions for my case. So, my dataset looks like the ane below.

Zone one ii 3

a thirteen 12 75

b 98 0 ii

c 0 100 0

I demand to remove the "0" values and for zone ane create as many rows as values I have. So, the final format should await like the following,

Zone

1 xiii a

1 98 b

2 12 a

2 100 c

3 75 a

3 2 b

Could you please advise me how to do that?
- Hi,
  Your request goes across the communication nosotros provide on this weblog. This is a complex solution that cannot be establish with a single formula. If yous take a specific question almost the functioning of a function or formula, I volition attempt to answer it.
Larry LeBlanc says:

Tin the input range to a listbox form command be the spill range of =UNIQUE(....)?

I want the input range to a list box form control to be all the DIFFERENTsales reps in cavalcade A without duplicates.
- Hello!
  Excerpt unique data to any empty column. Utilise this instruction.
  Then, using the formula, get a list without empty cells:
  
  =OFFSET($D$1,,,COUNTA($D$1:$D$200))
  
  Copy the unabridged formula from the formula bar.
  Select jail cell, which is where you want the dropdown to be shown.
  Go to Information > Information Validation.
  Paste the formula as the Source.
  I hope I answered your question. If something is still unclear, please feel costless to ask.
Shane says:

I'thou trying to use a range from another sail for the assortment and criteria. Information technology looks like this:

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

I'm getting a #Value mistake. Does referencing other sheets simply non work?
- Hello!
  Unfortunately, without seeing your information information technology is difficult to give you whatsoever advice. But the FILTER function was used with an error. Please check out the following commodity on our blog, information technology'll be certain to aid you lot with your chore: Get a listing of unique values based on criteria.
  I hope I answered your question. If something is nevertheless unclear, please experience free to ask.
- Shane says:
  
  I really figured out the trouble and have it working now. But I accept a new question. Is at that place a fashion to just have it cheque every canvass in the workbook? Hither is my formula at present (each canvass is a date):
  
  =UNIQUE(FILTER('fourteen-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 tin come across, this is actually messy. The other trouble is, new sheets will be added as time goes on. That's why I'm wondering if at that place'south a way to have information technology bank check E2:E219 in every sail rather than having add together each canvass individually.
Hudson says:

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

Yous see I accept 3 columns and I want to brandish all rows that incorporate the Keyword in either Columns B or C.

Cheers.
Mekan says:

Hi cheers for detailed explanations and enormous work you guys put to create this resource! I take a quick question:
For example I have a list of t-shirts. I want to filter t-shirts by colour and price ("blueish" and "above 5 dollars"). Notwithstanding, I at the same filter I want too 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!
- Howdy!
  I hope you have studied the recommendations in the tutorial above. Information technology contains answers to your question.
  
  =FILTER(A2:C7,B2:B7="blue")
  
  =FILTER(A2:C7,(B2:B7="blueish")*(C2:C7>5))
  
  =FILTER(A2:C7,C2:C7>v)
  
  A- name, B - color, C - price
  - Mekan says:
    
    Thanks for your answer Alexander! Unfortunately, your solution requires three separate formulas. However, I desire to give an option to filter "all colors" also equally individual colors in the same formula.
    
    =FILTER(A2:C7,(B2:B7="{blue, red, yellow, all colors}")*(C2:C7>5)).
    
    I want to requite a user something similar drop down list where he/she tin can select color from the listing "blue", "red", "yellow" every bit well as "all colors". I tin do individuals colors, but I can not do "all colors" option. I also tried to use "*" symbol, but FILTER role does not support wildcards.
    
    I would appreciate your comment/solution to this trouble! Thanks!
    - - Mekan says:
        
        Hullo Alexander,
        
        Thanks for your comments and assist to primary excel. I was able to effigy this trouble out using IF statements together with FILTER role. But in case everyone came across with a similar situation beneath how I solved the trouble:
        
        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 information input table 1 for Toll (B1) and one for Color where you tin can select individual colors and "ALL COLORS" from the drop down list (I used Data Validation- Listing).
        
        Stride three. I created a below formula in prison 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 every bit the post-obit:
        
        If color is not selected every bit "ALL COLORS", then filter table based on Price and Colour, else filter based on Toll only.
        
        At present I tin filter T-shirts based on individual colors also equally price only. I know information technology might audio elementary only it took me few days to research and recall near it.
        
        I would be happy if this could help to save someone'due south time! Have a bang-up twenty-four hour period! :)
PAUL TALTY says:

Hi,

splendid article, give thanks 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 sure criteria, but tin can I also get an average of their score for each 24-hour interval.

Thanks,
Paul
Kamil Stachurski says:

Howdy, I dearest 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 jail cell, simply when I try to utilize information technology in information validation --> list --> source information technology says 'The Source currently evaluates to an error".
(AA3 is where the user is supposed to select the first value and I would similar them to simply come across relevant items from the list BtoB1 list in cell AB3)

Cheers,
Kamil
- Hello!
  Unfortunately, without seeing your information information technology is difficult to requite you any advice. Please check out this commodity to learn how to create a cascading drop downwards list
iso says:

Howdy, excellent commodity, cheers!

Is in that location any way to limit the number of rows a FILTER function returns? For exmaple, the part returns 10 rows but I have only infinite for 5 (and I cannot delete the data beneath that)... is this viable?

Thank you!
- Hello!
  
  What an interesting question! Aye, it is possible, and I take created a separate example for other users to know :)
  
  Please see How to limit FILTER results
Pauline says:

Howdy,
Probably an piece of cake question:

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

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

Thanks in advance.
SJ Sellers says:

Keen post! This is and so powerful and I learned so much!!

Is there a way to Filter Non-Side by side Columns and define the Array Abiding using a formula (or in some way dynamically)?

Hither'due south what I mean: I would similar the {1,0,1} to change to {1,1,0} based on other criteria in my workbook.

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

Can change to:

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

Is this possible?
Thanks again for this bully post!!!!
- Hi!
  
  It' hard to say anything certain without knowing exactly what kind of criteria yous have in your workbook. Anyway, I believe the simplest arroyo that yous tin can effort is nested IFs.
  
  For example, if K1=1, render {1,0,ane}, if K1=2, and so return {1,1,0}:
  
  =IF(K1=1, FILTER(FILTER(A2:C13, B2:B13=F1), {i,0,1}), IF(K1=two, FILTER(FILTER(A2:C13,B2:B13=F1), {1,1,0}),""))
  - SJ Sellers says:
    
    Thank you Svetlana. That is not quite what I'thousand hoping to attain. I would like to create the Array Constant Dynamically. So in this instance, cells A1, A2, A3 might contain 1'due south and 0's that would be used to populate the Array Constant. So if A1=1, A2=0, and A3=one, the Array Abiding would be {1,0,1}
    
    In short, I would like to build the {ane,0,i} parameter using a formula.
    
    Is this possible?
    
    Give thanks you again! SJ
    - 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.
      - SJ Sellers says:
        
        OMG Svetlana y'all are the absolute best!!!!
        
        One more question. This works when I use a horizontal range but not when I use a vertical range. Merely curious why? And whatever mode I tin work around this?
        
        As an instance:
        =FILTER(FILTER(A2:C13, B2:B13=F1), J1:L1)
        works fine
        
        =FILTER(FILTER(A2:C13, B2:B13=F1), J1:J3)
        does not
        
        Thank you lot so much - this has save me countless hours!!! Y'all are like an Excel Angel!!!
        
        Thank you :)
        
        As for your question, it'southward 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 lot transposed your table), then the FILTER formula would crave 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.
        
        Nosotros have a couple of in-depth tutorials on Excel arrays and traditional CSE array formulas. If you lot are curious, you lot can check them out:
        
        Excel array formulas, functions and constants (among other things, it explains the difference between vertical and horizonal arrays)
        
        Excel assortment formula examples for beginners and avant-garde users
Randy Robertson says:

And so how exercise you make it piece of 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 information technology FAILED!!!!
Here is the macro
Sub examination()
Range("I4") = Filter(Range("A2:C13"), Range("B2:B13") = F1, "No Results")
Finish Sub
Run-fourth dimension error 'thirteen': Blazon mismatch
Raf says:

Hello, your guides are amazing.
I have a question: is it possible to connect two filter arrays es.
FILTER #1:
a - 1
b - two
c - 3
FILTER #2:
d - 4
e - 5
Connected Filters:
a - 1
b - ii
c - three
d - four
due east - v
Thank you :)
- Raf says:
  
  I mean without VBA
- Howdy!
  If I understand your task correctly, pay attention to the following paragraph of the commodity higher up — Filter with multiple criteria (AND logic)
  If this is non what you wanted, then delight analyze your question in more detail.
  - Raf says:
    
    Thanks for the answer.
    I mean some other thing:
    I have 2 different starting matrix that I filter with the FILTER function.
    In the way I obtain two 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 - three
    d - iv
    e - five
    Without using VBA
    - Hello!
      If I empathize your problem correctly, yous want to combine the results of the 2 filters. You can use the recommendations from the section above — Filter with multiple criteria (OR logic)
      Hope this is what you lot need.
      - Raf says:
        
        Not exactly.
        Every bit I said the starting matrices are ii and are dissimilar, so the FILTER functions refers to different matrices.
        Here is an exemple:
        =FILTER(A1:B10;A1:A10=5)
        =FILTER(AA1:BB10;AA1:AA10=5)
        Equally 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?
        
        How-do-you-do!
        Merge an Excel spreadsheet using formulas is not possible in your case. Y'all can learn more most it in this article.
        We accept a ready-fabricated solution for your task - Re-create Sheets Wizard. It is available as a role of our Ultimate Suite for Excel that you tin install in a trial mode and cheque how it works for free.
        If there is anything else I tin can assistance you with, please let me know.
        
        Raf says:
        
        Thank you Alexander
- Jose Cifuentes says:
  
  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 amuse in Sheets, I'chiliad still trying to figure out how to make it piece of work in excel.
Marker F says:

Is information technology possible to use the Filter Function to filter on format, in this specific example, color? I'm trying to avoid VBA for this specific workbook.
Cheers
Swapnesh says:

I'one thousand trying to enter this function into an xlsm sail whic has other macros. However, information technology is returning fault #Value... any help would be really appreciated

Im unable to upload my file or could have shared the aforementioned as well
- How-do-you-do!
  Check the information in the cells y'all 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 assist me sympathise your request better and discover a solution for you.
  See too this section above.
Ram G says:

Dear Sir,how to filter data ,(Like Company Record of Daily Expanse to Employees,i want to select merely ane date and the all associated information of that mean solar day will show by choosing date)
- Hello!
  If the required date is written in a cell (for instance, F1), then you can employ the formulas from the second section of this article. If yous desire to write a engagement into a formula, use the Appointment office.
  I hope I answered your question.
Cameron Stewart says:

Regarding using amass functions wrapped around filter results ... gear up {is empty] to null works fine for near aggregate functions such as SUM, just 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. Perchance I am missing something, merely this is a lot harder to do than I idea. Peradventure I need to abandon the FILTER and UNIQUE function to use other methods. Whatever suggestions? Thanks
- Hello!
  Yous have non listed the formulas that are causing the problem. But I think using function IFERROR will aid replace the error message with zero.
  If there is anything else I can help you with, please let me know.
Jim says:

How practise i create a filter on an ever changing range (sometimes 100 rows, sometimes 250, or even x) - do i accept to preset the filter to encompass x number of rows, or tin can i create dynamic range
- - Gaurav says:
    
    Howdy Alexander,
    
    Tin can we filter by connecting to a dynamic range via a pin table?
Tripp says:

With is formula I pull all rows for the week and corresponding times. I sort on the calendar week. Is it possible to add together a bare line to separate each day of the week? My spreadsheet has 85 columns, I bear witness cavalcade one (engagement) and column 2 (key fourth dimension). I also need sort both the Date and Key Time. Thank you for whatsoever assist.

=FILTER(SORT(FILTER(TDaily[[Engagement]:[Fundamental Time]], (TDaily[Engagement]>=I10) * (TDaily[Date]<=I11)), ane, ane), {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})
- Hello!
  The FILTER function returns an array of values. Therefore, information technology cannot return any empty lines. If you create a pivot table from this data, and so different formatting methods are possible at that place.
Manuel Isaías says:

Howdy Lady,

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

Example:

List of criterias
A1: 1
A2: 2
A3: iii
A4: four

The information i want to filter:
B1: 1
B2: iv
B3: 3
B4: vi
B5: seven
B6: ii

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

Event:
1
4
3
two

is at that place a manner to exercise this?

Thank you in advance!
Matt says:

Hi,

In your examples, how practice you return just Proper name and Wins for example
Lincoln Oliver says:

Hullo!

One doubt:

How practise I Add a last row with column sum, using filter? Could I do that?
- Hello Lincoln,
  
  This can exist done by using the SUM role with a spill range reference (# symbol).
  
  Bold the FILTER formula is in E4, the SUM formula will exist as simple as this:
  =SUM(E4#)
  
  Only make sure there are enough empty rows between the FILTER and SUM formula cells. If FILTER does not accept the required number of rows to fill up with the results, a #SPILL fault will occur.