In this post, I want to share a few more advanced filter options, such as working with dates and using OR logic.

If you’ve read my getting-started article on the Filter function in Google Sheets, you’ll know that it’s a very powerful function when working with data in Google Sheets. In this post, we’ll take it one step further and look at more advanced logic with an OR condition.

Click here to make a copy of the advanced filter worksheet.

## Advanced Filter Examples in Google Sheets

### Example 1: Using Reference cells and Aggregation

Here’s an example using the FILTER function to aggregate (count and sum) how many values lie between two dates. I’m using the fictitious data in the template sheet with a date and associated value for that day.

In this scenario, we want to count how many values occur in December 2017 and then sum their value, using the FILTER function:

To count the values, first put 12/1/17 and 12/31/17 into two adjacent cells and then try this formula in the next cell, as follows:

`=COUNT( FILTER( $A$2:$A$95, $A$2:$A$95 <= $E$2, $A$2:$A$95 >= $D$2 ))`

### Example 2: Use nested functions inside the Filter function

We can make the above formula more concise by nesting the MONTH function inside of the Filter function:

`=COUNT(FILTER($A$2:$A$95, MONTH($A$2:$A$95)=12))`

and it will give you the same answer of 31:

### How do I sum filtered cells in Google Sheets?

Use this formula:

`=SUM(FILTER($B$2:$B$95, MONTH($A$2:$A$95)=12))`

We’ve done two things:

i) changed the filter range to column B (but not the test conditions!) to get the values, and

ii) wrapped the filter function in a SUM formula instead of a count.

The result is now:

### How can I use OR logic inside an advanced filter?

If you know the trick for this advanced filter, this is quite easy to do, but if you don’t, well…

…then it’s easy to get lost in nested OR functions inside of FILTER functions and end up stranded in the land of errors.

In this example I have 10 rows of data in columns A and B, as follows, and I want to retrieve all of the “alpha” and “gamma” values.

Rather than running two separate filter formulas, and then combining the results, we can use one formula that gives us the desired output:

`=FILTER(A1:B10,(A1:A10="alpha")+(A1:A10="gamma"))`

**We use a “+” instead of a comma to handle the OR logic.**

### How does this advanced filter formula work?

Consider a basic FILTER function like this one, for example, which returns only data with “alpha” in column A:

`=FILTER(A1:B10,A1:A10="alpha")`

Now, let’s see how the advanced filter works.

The next step is to return all the data that matches “alpha” or matches “gamma”.

We do this by using a “+” as the boolean operator OR inside the second argument of the FILTER function, as follows:

`=FILTER(A1:B10,(A1:A10="alpha")+(A1:A10="gamma"))`

Now, anytime either condition of the filter is satisfied, in other words an “alpha” or a “gamma” in column A is present, then that row of data is returned in our output filtered dataset.

## Other resources

Discussion on Stack Exchange about using AND and OR conditions in the FILTER function.

Check out my getting-started post on how to use the Google Sheets Filter function to work with data.

Have you found a use for this advanced FILTER + OR formula? Any questions? Let me know in the comments below!

Hi, phenomenal walkthrough! If I wanted to refer to a cell instead typing in the text how would I go about doing that? For instance instead of “alpha” what if you wanted it to reference A1 where the “alpha” data resided? I keep getting formula parse errors. Thank you much!

Hey Dan,

You can just change the formula to this:

`=filter(A5:B14,(A5:A14=A1)+(A5:A14=B1))`

where I have my data range in A5:B14, and my filter values in A1 and B1 (e.g. alpha or beta or gamma etc.)

Hope that helps!

Cheers,

Ben

what if my criteria range has different set of words such as,

Alpha A, Alpha B, Alpha D and i want to filter everything that contains Alpha.

=filter(A1:D, regexmatch(A1:A, “Alpha”))

here’s a source:

https://infoinspired.com/google-docs/spreadsheet/regexmatch-filter-criteria-google-sheets-examples/

Hello Ben,

Could you tell me please “How to use Filter with Arrayformula or any Alternative?”

Thanks

Ben, thanks for this. Have a question for you. What if my OR conditions were more complicated? Like, what if my filter is for multiple conditions to be met for both sides of the “or”? I’ll try to explain what I mean. For instance, what if I want it to filter something based on whether A1:A10=”alpha”, B1:B10>5, or if A1:A10=”gamma”, B1:B105)+(A1:A10=”gamma”,B1:B10<5)) but that doesn't seem to be working for me. Any help?

Did you ever figure this out? Asking for a friend 🙂

Use one condition argument for FILTER and *. To create more complex conditions, bracket as necessary.

The article didn’t explain exactly how the + works. Whenever Google Sheets expects a boolean, it is looking for a TRUE or FALSE value. 0 evaluates to FALSE and all other values evaluate to TRUE.

Using +’s between n operators gives you 0 if none are true, n when n are true (more importantly, not 0).

In a similar vein, using the * to add an AND condition works because it multiples your current result by either 0 (if the condition is false) or something bigger than 0 (if true). i.e. If your condition is not true, then your entire evaluation will end up FALSE.

Here’s an example using the article’s data to get all values that are alpha or gamma, but only those greater than 4:

=FILTER(A1:B10, ( (A1:A10=”alpha”) + (A1:A10=”gamma”) ) * (B1:B10>4) )

Thanks for your comment, Rohan! Very helpful addition

thank you so so much for the explaination

Hi All.

Ben I love your work.

Rohan thanks for your addition, very helpfull.

Does anyone know how to make the formula work if one or other result isn’t found? for example I have this filter =FILTER(Accounts!A3:F500,(Accounts!B3:B500=C6)*(Accounts!C3:C500=C7))

C7 and C6 have data validation with account codes and product codes respectively at the moment it works well if I want to filter all the products of a certain type that are from one or other account but I would like to be able to delete one or other criteria and so it shows for example all transactions from the specified account regardless of product type or all products of that type regardless of the account it came form. can I make filter do this?

Thanks Rohan, this was exactly the explanation I needed to filter using both AND and OR logic.

Hugely helpful comment. I’ve been searching the web for an hour for how to do this.

Amazing! Really helps me build out a simple database from a master sheet using different conditions.

Hi Ben

Is it possible to set a “named range” as the filter criteria?

Let’s say I want to use the “OR” function shown in the example, but I have a list of 50 items I want to include in my filter. Do I need to build up 50 “OR” segments in the formula, or is there a way to link to a named range?

Hey Peter,

You can use a named range in a filter, but it won’t solve this OR issue.

I’ve not found a way to succinctly do this, other than building up the 50 OR segments. You can use a formula like this to build the 50 segments as text, which you can then copy paste into your filter formula:

`=ArrayFormula(mid(concatenate("+(A2:A11="""&transpose(unique($D$2:$D))&""")"),2,len(concatenate("+(A2:A11="""&transpose(unique($D$2:$D))&""")"))-13))`

I’ve not found a way to embed this in the filter function however.

If I find anything, I’ll post it in the article here.

Cheers,

Ben

Try here for dealing with queries using multiple OR’s

https://docs.google.com/spreadsheets/d/1anIWkpQ2Ke7t6nILOKDZDFZhfaYQbqBMPmf_t1cwf9E/edit?usp=sharing

Hi, Thanks for all the help, my problem it’s a little bit different, I want to syncronize a Google form with a Google Sheet, I know it is easy, but also, I want to create another sheet that automatically update the Date in one cell, for example: you send “Alpha” with the google form with a date, then you send “Beta”, Beta has his own cell, but, how do I create a Filter that update the date when you send “Beta” or “Apha”? Thanks for all the help!!

Great post Ben

Thanks a lot

Greetings,

Can the OR filter be used in a filter view, which would result in your original data set (A1:B10) being reduced to where the only viewable/visible data then becomes what you have in D1:E5 (but would appear in A1:B5)? Hopefully I’ve accurately described what I’m trying to accomplish. I have data that will potentially encompass A4:AP401 in a my spreadsheet (as more an more data gets added). I wish to filter out that data which is entered by specific people (name of person who entered the data appears in column C). That is why I am trying to create a filter view. One option is to create it via Value, whereby the people’s names can be checked off.

The issue I have come across is that method not cumulative. What I mean is that if the data set is to be populated by Jon, Joe, Bill, Dave, Lisa and Ted, and I wish to only see the data entered by Jon and Joe, I have to create a filter view and then manually go in can check off their respective names. However, if Jon enters data, but Joe has not yet, only Jon appears as an option to check off. Which means I have to wait until Joe enters data, go back into the filter view, and check off his name, so then his subsequent data will appear. What I want to do is set up a filter view ahead of time so that whenever Jon and Joe enter data, they automatically appear in the view (without having to go back in and manipulate the filter view). Thanks for your time.

Thank you for this guide! It seems to be exactly what I’m looking for, however I’m running into a bit of trouble. I’ve tried these formulas:

=filter(Input!C8:O,(MATCH(Input!I8:I,INDIRECT(D8),0))+(MATCH(Input!I8:I,SPLIT(C8,”,”),0)))

^ gives me #N/A “No matches are found in FILTER evaluation”, though there should be.

=filter(Input!C8:O,(Input!I8:I=INDIRECT(D8))+(Input!I8:I=SPLIT(C8,”,”)))

^ gives me #VALUE! “FILTER range must be a single row or a single column.”

=filter(Input!C8:O,OR(MATCH(Input!I8:I,INDIRECT(D8),0)),MATCH(Input!I8:I,SPLIT(C8,”,”),0))

^ gives me #N/A “FILTER has mismatched range sizes. Expected row count: 994, column count: 1. Actual row count: 1, column count: 1.” (The Input sheet has 1001 rows, with data starting at row 8. The sheet with this search function is 1000 rows, and the cell where this function is being tested is C18.)

Input!C8:O is my spread of data, Input!I8:I is a column listing specific colors. INDIRECT(D8) refers to a named range; the cell D8 has a Data Validation list of text options, and when one is selected (ex. “Greens”), INDIRECT fits it into the function to draw data from that named range (ex. Green, Emerald, Seafoam). C8 is a cell with multiple text criteria separated by commas, ex. “Red,White,Blue”. I do intend to add more criteria to search other columns, but for now the goal with this formula is to look at column I on the Input sheet and find all rows that have Red, White, Blue, *and* all Greens in column I and return that data.

The first formula seems most promising as I can get it working exactly as intended if only one of the criteria is present (and either set of criteria works), but once I try to search for both at once, it’s errors everywhere. QUERY would probably be a better option for searching with this criteria, but it seems the data shown by QUERY doesn’t carry over hyperlinks, so I have to stick with FILTER. I can’t seem to find what I need just by Googling, so if you know of a fix or any suggestions I could try, it would be greatly appreciated!

Hi Ben,

Thank you for the article. I have followed your example however it still is coming back with an error. My formula is

`"=FILTER('House Buying Analysis'!A:V,'House Buying Analysis'!(D:D="Richmond") + (D:D="Burnley"))"`

The error is “Formula Parse Error”.

I was just wondering if you had any idea why it is not working? Thank you in advance!

Hey Cody,

The issue is with how you’ve written the ranges in the filter criteria, because they’re presumably in a different tab than your formula. Try this version:

=FILTER(‘House Buying Analysis’!A:V,(‘House Buying Analysis’!D:D=”Richmond”) + (‘House Buying Analysis’!D:D=”Burnley”))

Cheers,

Ben

HI dan-

This is very helpful. I’m having trouble however trying to find the mode of one column based on filters of other columns.

For example I need to find the mode of Column N, based on the contents of column X, N and B.

B=1

N=Stores

X=discounted

Y=values of discount (for which i need the mode.)

Do you have any suggestions? Thank you so much in advance 🙂

I HAVE A TWO SHEETS ONE IS CUSTOMER SHEET AND OTHER IS CODE WISE PRICE DETAILS .I WANT TO SEARCH RECORD BASED ON A CUSTOMER AND CODE PLZ GIVE DETAILS OF FUNCTION.

With regard to using a named range or indeed CSV text as filter values rather than hard coding them as above I enclose three slightly different variations on the OR filter idea without the OR!, rather using MATCH instead which would seem to be the more obvious approach since it is a matching exercise.

The suggested solution may be found at

https://docs.google.com/spreadsheets/d/1gKJPJeN7DHNfSO1F5Qv3u1pf9PY484ceEIiQr5MVdeM/edit?usp=sharing

Regards.

John B

Yes thats useful. In fact without arrayformula also its working. I tried.

So it solves following question:

Que: Instead of using hard coded values with many OR conditions, Can we use a range (multiple values) to compare in a filter condition?

Ans: Yes!

In Filter(range, condition,…) use condition like below:

isnumber(MATCH(Inquiries!K2:K,Active,0))

Here Active is a named range with many values in a single column.

I’m curious how one would do this on an importrange logic in another workbook like this:

=FILTER(IMPORTRANGE(“sheetkey”, “Checks”), INDEX(IMPORTRANGE(“sheetkey”, “Checks”),0,8)=”ABC”)

the below link is shared publicly

https://docs.google.com/spreadsheets/d/1hWGAaDTnR_08OcB950dPczHpjvuHyVpohghOQD8QTW4/edit?usp=sharing

If someone could help me come up with a filter that shows any occurrence of the word Blue in either Column B OR Column C

Thank You in advance.

Did anyone answer your question?

I have the same question.

In case you still needed an answer, I edited the spreadsheet at the link and was able to create a formula that hopefully points you in the right direction.

=FILTER(A2:C11,REGEXMATCH(B2:B11,”Blue”)+REGEXMATCH(C2:C11,”Blue”))

Hope it helps!

John

Hi Ben,

The data is filtering in perfectly except that once it is filtered, I am having a problem filtering the data A-Z or Z-A.

For example, I am using a master spreadsheet with data headers “Name, Venue, Time, Date, Review.” From that master spread sheet I am filtering data by Name into individual sheets. Example sheet 1 is “Joe Smith” and all “Joe Smith” data is filtered from master sheet to sheet 1. Once all that data is filtered in, I am having difficulty filtering “Venue” from A-Z or Z-A. I have set up the filter on sheet 1 but it does not perform the function when commanded. Any help???

From what I can figure out from my experimenting, the sorting (A-Z/Z-A) is linked to the sorting in the master sheet, so if you switch the sorting in the master sheet to “Venue”, you should find that this also happens in Sheet 1.

Ben Collin,

Congratulations for you expetise, dedication e colaboration with the community.

In due course, I will post an issue where your experience could be of extreme value.

Hi Ben,

Is there a walk around where we multiple conditions are optional and not always selected in drop down.

Example for filtering student data

Data validation A1 is Age, B1 Class, C1 City, D1 Gender

=FILTER(Data!A:D,(Data!A:A=A1)+(Data!B:B=B1)+(Data!C:C=C1)+(Data!D:D=D1))

can we get results even when all 4 options are not selected matching any 1 or all can filter results

Is it possible to combine filter and sort. I would need to first filter the data and in a second step sort it according to a specific column.

Hi Ben,

Is there a way to use the filter function results as a reference for a data validation list of values ?

Does someone have a formula I can apply to Google Sheets that filters data in one sheet based on the column values of another?

I’m getting “That function isn’t valid” when trying to execute anything similar to =count(filter(range,range=”criteria”))

Hi – I’m trying to come up with a filter that copies a row from main sheet (ALL DATA) if the date (timestamp) in Column A is 30 days or older and if the status in Column P is “Archived”. I’ve come up with the following, but it doesn’t work:

=filter(ALLDATA!A:S, “select P, where (dateDiff(now(), A) > 30) and P!=’Archived'”)

Can anyone assist?

Hi,

I would like to ask for help with following formula creation.

On the tab “Dashboard” I have 3 data validation lists to get averages based on the selection. I created there option for “ALL” however I am not able to figure it out the formula to show me results (averages from all) when selecting “ALL”. I tried to use OR…but no result.

Thanks for help.

example:

https://docs.google.com/spreadsheets/d/1Owxgnek2L-SNnjPHs6qcZ-0YUrfIBahd8NPSKjg7sgc/edit?usp=sharing

Did you ever get a reply to this question?

I am trying to do the same thing – if a confition is met, then filter for this, otherwise do not field (i.e. keep all)

Hi, I would like to ask for help with a filter function I’m having trouble with. I don’t know if it’s directly possible, but I would like to create a sheet that compiles rows of data from 2 other sheets. I have the functioned created for one sheet: =filter(Programs!A:O,Programs!$E:$E=”Architecture”)

but none of the variations I’ve tried will allow me to add a 2nd sheet to the results, an example of one attempt that doesn’t work: =filter(Programs!A:O,Programs!$E:$E=”Art”, [Series!A:O,Series!$E:$E=”Art”])

Thanks!

Tod,

I’m trying to do the same exact thing. Did you figure this out?

Hi Ben

I’m using a google form connecting to this sheet. https://docs.google.com/spreadsheets/d/1ImF1rOZYL3tiLDFYzb_KgDt_mzSJFDWOJLkmreLzrYI/edit#gid=871509352

I’ve tried using filters in column Q of that tab, to move or copy the role into the corresponding tab, like this: https://docs.google.com/spreadsheets/d/1ImF1rOZYL3tiLDFYzb_KgDt_mzSJFDWOJLkmreLzrYI/edit#gid=1572613331 – but can’t get it to work.

Any ideas?

Cheers

Andrew

is there a way to use like condition in custom filter? for example i want to filter a column where the text like ‘%text%’, all above example s seems to be for absolute value

Thank you for this guide, Ben! It seems to be really close to what I’m looking for, however I’m running into a bit of trouble implementing a line break insertion while trying to join the output of my formula into a single cell as a multiline list of values.

My formula

=IF(A2=””;””;TRANSPOSE(SORT(FILTER(‘Sheet1′!$B$2:$B;’Sheet1′!$AA$2:$AA=$A2;’Sheet1’!$AB$2:$AB=$B2))))

extracts a few values and puts them into a few columns of a line but I need them combined into a single cell with CHAR(10) for line break.

Please help me to do that.

Give me a help. I want to filter results which appear in column A or column B . For example , I have sheet Data . I want to find all results have text Ex in column A or Ex in column B and have text 1, 2 or 3 in column C . If I use this formula: FILTER(Data!A:C,(Data!A:A=Ex)+(Data!B:B=Ex),(Data!C:C=1)+(Data!C:C=2)+(Data!C:C=3)), is it ok??? I think it is logical but it didn’t bring right results 🙁

I am having trouble with filtering in my sheet. I would like to have the sheet show me the rowes that have phone numbers that begin with only certain area codes. I can do it in excel but want to know how to do this in my GoogleSheets page. I used this formula

=FILTER(A1:L155,C2″216″,C2″440″,C2″330″)

and was retured an #ERROR! (Formula parse error.)

Can you assist me with this?

I have a problem with FILTER, in that it does not seem to work when the filtered data is pulled in with IMPORTRANGE, e.g. =Filter(IMPORTRANGE(“https://docs.google.com/spreadsheets/d/abcdgobbledegookxyz”,”Table!A2:S170″),P2:P170=”Stuff”) always says Error – No matches are found in filter evaluation.

Why doesn’t this work?

Gary – You have to give permission to the importrange before you can nest it in other formulas. So try importrange on its own, allow it, then wrap with filter. Should work!

Cheers,

Ben

Is there a way to use the filter formula to:

1) Compare Column A to Column B

2) Remember the unique values in Col B that do not exist in Col A

3) Compare the unique values found in Col B and then compare it to Col C

4) Output any duplicates find in Col C to Col D

I tried using variations of =MATCH function but it only checks if there is a matching pair.

The formula I used:

=FILTER(trim(B2:B),trim(A2:A<2), match(trim(B2:B),trim(C2:C)))

For example, if Col A has the value "test" a single time, but Col B has "test" entered twice, it will only recognize the first instance of "test" in Col B as a match, not the second instance (which makes sense, I guess).

Also, the other problem with my formula is that if there is a number value like "1", it will return "1" as a result even if it's not a duplicate. I suspect because I'm calculating match(A2:A<2), which "1" certainly qualifies.

Hi Dan,

I’m having a hard time on this. For the examples above, they only have 2 criteria to filter. What if they have 3 criteria to filter? Can’t seem to figure it out.

A B C

Alex Break 1 16

Ben Break 2 17

Dan Meal 62

Alex Meal 60

Ben Break 1 15

Dan Break 2 17

Say threshold for Break 1 and 2 is 15 and for meal, 60. How can I filter that it would only show the Break >15 and meals >60.

I’ve used:

=filter(A:C,(B:B=Break 1)+(B:B=Break 2),C:C>15)

I don’t know where to insert the filter criteria for meal >60.

Hope to hear from you soon.

Not sure if this will help anyone… But I finally figured out how to get INDIRECT(), ARRAYFORMULA(), and FILTER(), working together to reference values from another sheet.

I like to use a counta() formula to get the exact row count because too often I have had formulas break because someone put data in the wrong place, And due to the fact that I have gotten into combining the formulas with apps script, I found this very useful in getting rowData.

My Formula:

=ArrayFormula( IF( Indirect(“‘Daily sheet'”&”!”&”A4:A”&G1)””,ArrayFormula(‘Daily sheet’!$B$2-‘Daily sheet’!$A$4:$A),””) )

Where G1 holds a COUNTA() formula to get a flexible row count from the daily sheet, tucked inside an array formula that only grabs the rows of data and not the blank spaces.

Hope this will help someone else.

This is really great. But I only need a few columns from a very wide sheet in my output. Is there a way to include non-contiguous columns in the filter output? something like filter((a1:e3,n1:p3),condition)? Thanks.

Hi

I am trying to multiply the result of a cell but only want to multiply the 2 decimal points that are showing. Rounded amount.

A1= 3.657 cell is formatted to show 2 decimal points A1=3.66

I want my result to equal $10.00 * 3.66 = $36.60

The result I am getting is $10.00*3.657 = $36.57

Can someone assist me?

Thank you

Hey Linda B,

I believe I can help with this. If you are wanting to leave your original A1 cell alone then this is what I would do. In the cell that you are multiplying by 10.00 in try the formula “=ROUND(A1,2)*10.00”

This here should return your desired $36.60. That is provided that you format the cell to be currency.

I hope this helps!,

Matthew

Hi,

Is there any way to get multiple rows from a google sheets.

For Example if I want to pull data from a sheet where I have 2 or 3 entries with same name. How I can pull data of all 2 or 3 names please.

Thank you.

Hi,

So I have sort of a complicated question that I have been working on for awhile now. So what I am trying to do is return a specific value when a list of dates reaches a date that is greater than or equal to a specified date. In other words I want this certain calculated value to appear once a certain date is reached or a date past the specified date is input into the row of cells that track the dates.

My dates start in A2 and the specified date in this case is F5 which is equal to 1/15/2020. The value to return let’s say is the formula H2*12.5 if the date is reached or passed. Otherwise I would like the cell to remain empty. So far I have tried this formula:

“=IF(IFNA(FILTER($A$2:$A$35, $A$2:$A$35>=F5),””)>=F5, H2*12.5,””)” however this is not properly tracking correctly I would really love some help with this

Thank you for your time.

hi, how can I translate the vba advanced filter code I have mentioned below in the google apps scrip.

——————-

Sub Makro2()

Sheets(“veri”).Range(“A1:C24”).AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range(“sonuç!Criteria”), CopyToRange:=Range(“A6:C6”), Unique _

:=False

End Sub

Hi ben i am having an issue, my filter formula is reading from other cells/list but is only reading when there’s input as numbers, i need to read any text on that list, i tried changing formats on the columns but still not working. What could be the issue?

Hello Ben, great post always!

Please, does anyone know how I can ensure only certain columns are returned using the filter formula. I am trying to filter based on a specified cell value of a particular column in another sheet but I want some columns excluded from the returned results. Thank you

Try using the QUERY function instead of the filter function. It lets you specify which columns you want and you can do your filtering with the WHERE clause. It’s a little tricky to use at first, but this article should help: https://www.benlcollins.com/spreadsheets/google-sheets-query-sql/

Ben,

I have raw data over a variety of dates with multiple entries on the same date. I’m trying to calculate the Standard Deviation of values located in Column G if the date (Column E) matches the referenced date in the summary section I am creating.

Example:

(E) (G)

1/1/2020 5

1/1/2020 8

1/1/2020 6

1/2/2020 4

1/2/2020 9

I am looking for the STD DEV of Column G values if they were recorded on the specified date in my summary table.

Should I be trying to use FILTER with the STDEV function or something else?

Hi,

I am trying to filter the ROW by not blank cells so as following,

FILTER(DATA!S2:W2, (DATA!S2:W2″”) + filter(DATA!Y2:AB2, (DATA!Y2:AB2″”))

So its either take value from S:W or Y:AB range either of those range would have data, but its not working !

Hi Ben,

I’ve already solved a few issues that you’ve provided answers to, and I sincerely thank you for your help.

However, I can’t seem to find an answer for my problem anywhere, or maybe I’m not asking the right question.

Let me give it a shot:

I have a database of records in sheet “Data”:

A: RecordNumber, eg. 1,2,3,4,5,6, …

B: Date, eg. 04/12/2018, 03/23/2018, 05/16/2018,…

C: TransactionType, eg. “Bill”, “Invoice”, “Credit”,…

D: Account, eg. “Repairs Engine”, “Repairs Body”,…

E: Amount, eg. $xxxx.nn

F: VehicleNumber, eg. 3302,3303,3304,…

In sheet “Select”:

I filtered all the unique months and years, (eg Apr-2018, May-2018, etc), unique transaction types, account names, and vehicle numbers and displayed them in columns with selection tick boxes beside each.

Selecting one or more tick boxes will put each of the selected values in cell 1, starting with column AA.

Then filter the database to produce a list of record numbers using the criteria in row 1:

AA2=filter(Data!A2:A,Data!B2:B=AA1)

AB2=filter(Data!A2:A,Data!B2:B=AB1)

Z2=sort({filter(AA2:AA,AA2:AA””);filter(AB2:AB,AB2:AB””)})

So, select Apr-2018 and May-2018.

Z AA AB

SelRecs Apr-2018 May-2018

1 1 2

2 4 7

4 6 12

10 10 25

25 34 41

34 56 66

41 78 88

56

66

78

88

First of all, put each selection criteria in a separate helper column is crazy – 12 months * 5 years = 60 columns??? No.

So how do I get to column Z without the helper columns?

Secondly, the solution to the above leads to solving the next problem, and that is, after selecting any number of criteria, how do I display the records efficiently?

For example, I could put a seperate filter function at the top of each display column (there are 37).

I tried using the query function, but I would have 37 conditional statements.

Any suggestions?

I thought these might work, but they didn’t:

=filter(Data!B2:B, Data!A2:A=Select!Z2:Z)

=arrayformula(filter(Data!B2:B, Data!A2:A=Select!Z2:Z))

=index(Data!B2:B, match(Select!Z2:Z, Data!B2:B, 0))

I am trying to filter the data on one sheet based on a reference dynamically changed row on another sheet

On the sheet to be filtered are 6 columns, in addition to other columns, each one of which is either a unique value or it is not. (Using the check box to toggle between a value and a “space”) the columns can be

| a | b | c| d |e | f | g | h | or

| | | | | | | | |

Or any number of variations in between each cells dual options.

The reference row in the target sheet has the same arrangement except that it toggles between the value and x (So as not to always be matching the source) Boolean but not using true and false

I can easily filter this by comparing The whole array of row with the reference row:

=iferror(filter(Allitems!A2:C,(Allitems!D2:D=C7)+(Allitems!E2:E=C8)+(Allitems!F2:F=C9)+(AllFilms!G2:G=C10)+(Allitem!H2:H=C11)+(Allitems!I2:I=C12)+(Allitems!J2:J=C13)+(Allitems!K2:K=C14)))

This formula ORs the elements so that any one value returns the appropriate row and additional just add on.

I can use the same formula with commas instead and only get rows that Exactly match.

What I want however is to get a result where all rows with multiple matching items designated by the reference row get returned. For example

The reference row has

Ref Row | a | | c | | | f | | |

Row 1 | a | b | c | | | f | g | h |

Row 2 | | b | c | d |e | f | g | h |

Row 3 | a | | c| d |e | f | | h |

Row 4 | a | b | | d |e | f | g | h |

I want the filter to result in rows 1 and 3 but no others to be in my filtered set.

The OR formula results in all of them in the result set

The AND formula results in none of them in the result set.

How do I accomplish this with Filter? (Query doesn’t work for me because I will be returning multiple html links in the result set.)

Thanks for the tutorial! It helped me a ton!

Is it possible to filter to a new sheet but add additional data to the remaining row? For example, I filter Name, Phone Number from sheet one to sheet two based on a simple condition. Now that the information is on sheet two can I add additional information like Address, Purchases, Anything, in connection to what’s been fileted from sheet one? Can I have sheet two essentially work separately and keep new information connected to the filtered data, especially if I need to use the simple filter (button) in regards to the new information? For example, I need all information on sheet two alphabetical based on the new Addresses.

FILTER(IMPORTRANGE(“url”,”Range!A4:J150″),IMPORTRANGE(“url”,”Range!A4:J150″)=”ONLINE”+IMPORTRANGE(“url”,”Range!A4:J150″)=”All”)

Hi, The above formula is giving me error(#na). Kindly suggest. I am looking to import data on multiple criteria.I tried to wrap the criteria in bracket, but its not letting me do so.

I need some help, please. I have several Google Sheets created in one doc., but need to sort within all of them. For example, there is a list of names on each of the 10 Sheets created and I need to sort by each individual name to create a Sheet with only each individual name. Does that make sense? I am trying to save myself time instead of cutting and pasting from each Sheet into a new one for each individual name. Thanks!

Thank you very much, it helps me a lot

i love that post Ben it help me alot to understand things

Hi, all!

Ben, thank you for the post.

I’m trying to figure out is there way for the filter function to refer to a sheet from different google document (workbook)?

I have registration opened which is exports to a google table and from there I need to filter and send each piece to relevant faculty.

But I need each of them not to see the other’s info – that’s why I made filter functon for each faculty and now i can’t find way to share it with them as a separate google doc because the filter formula works only in the same google workbook.

Is there solution for that?

Thanks a lot in advance!

Hi, you can use importrange function to get a data from other google workbooks. But you will have to use it with Query function instead of filter.

=query( importrange(“…”, “DETAILS!A1:F”), “select Col1 where Col6 = false”)

Yes thats useful. In fact without arrayformula also its working. I tried.

So it solves following question:

Que: Instead of using hard coded values with many OR conditions, Can we use a range (multiple values) to compare in a filter condition?

Ans: Yes!

In Filter(range, condition,…) use condition like below:

isnumber(MATCH(Inquiries!K2:K,Active,0))

Here Active is a named range with many values in a single column.

Hopefully you’re still active here! Hoping you can help.

I’m trying to create a filter that has multiple variables/filters.

So let’s say I wanted to filter my second page, onto the first one. First by state (column D). Sometimes that’s all I’ll need, but other times I’ll want to narrow the results down by city (column C) within said state. Eventually, I’ll be filtering as well, by equipment type in each of those filtered results.

For example, I’m looking for a truck in CA, type in CA to my state cell. Got the results, now I want to to find carriers in the San Diego area (is there a way to show results based on a radius?) or just out of San Diego. Ok, now I want to find Flatbeds, or RGNs, or Vans…

Sometimes I’ll even only want to search by state and equipment type. Sometimes SOLELY on equipment type (certain equipment is more specialized and trucks will drive wherever.

So I guess I’m looking to filter a filter of a filter, but be able to type into ANY of the state/city/equipment cells what I’m looking for, and have it show results…is this possible?

Hi Ben

Thanks for all your info so far with all this FILTER malarky. It is a great help.

I was wondering if there was a way to bring the results but merge the data into one cell, rather than listing.

For example, I have over 20000 rows of data with dates and jobs. On a separate sheet, I have successfully managed to filter a certain number of employees work history from the master sheet, with dates down the left and ID numbers along the top.

Unfortunately, on the rare occasion, 123456 Bob has done two jobs, and therefore I get the #REF error, as obviously on the next day he has another job and I cannot overwrite the data.

What I would like is a list of Job1, Job2 in the same cell, if possible. I’ve tried concatenates in the filter formula but cannot seem to get it to work. Maybe a JOIN would work?

well i try to do it but still not working

Hi Ben,

Is it possible to join/merge data from different sheets using the filter function?

Kind regards

Quero fazer uma busca na planilha B onde a condição Filter é = valor digitado na planilha A. Como fazer ?

Hi Ben,

Thanks for this. I’m using Filter function, one condition of my filter is a data validation dropdown, When I select one value from dropdown, it shows the results and again when I choose another value, it shows the new results but my previous results are also shown. How do I solve this issue?

Thanks,

Ram

Omg I wasted hours looking for a solution and all I had to do was put a simple “+” sign.

This is what I was looking for. Thank you!

wow “+” this is like a magic!

Thank you

Hi Dan,

I am needing to filter data from a table with 5 columns and I need to remove rows that have 3 cells that match other rows.

To put it into context, I am creating a master schedule for a basketball league showing every game that is played. This is done by using importhtml from each teams schedule and placing them all in one table. However, because each schedule comes from a different page there is a duplicate of each game the only difference being that the teams are in different columns. Any advice on how to remove these?

Hi. I hope someone can answer me. I am trying to make a schedule in Google Sheet with a calendar view. What I want to do is to have a drop-down of all the months and I click a specific month on that drop-down, it will only show a calendar schedule for that month only. For example, if I click March on the drop-down, it will only show a March calendar and if I choose another month(April) in the drop-down, the March calendar will be hidden and an April calendar will show up. Do you guys think it’s doable?

Hey Ben,

Thank you for these guides, they are really helpful.

I have a question, I’m running a filter function to filter a HUGE database of names. My question is: is there any way for the filter to function with approximate coincidences or does it only work with exact coincidences? This is a problem to me since the database is fed by multiple people and sometimes they can write the names on different ways e.g.: Peter Small Thompson/Peter S. Thompson.

I’ve been looking to solve this issue but I have not been able.

This could be really useful for what I’m doing, though can I ask, with the OR/+ function, is it possible to filter by multiple columns?

I notice what you did was look at filtering multiple requests from the same column, yet what I’m trying to do is filter the same value in two columns, i.e. I enter a value into D1, and I want to know where it appears in both columns A and B… Individually, not in both. So I want it to show me where it appears in just A, and where it appears in just B. So far, what I’ve got is this, and it isn’t doing anything:

=FILTER(SRTs!A3:H1000,(SEARCH(D1,SRTs!A3:A1000))+(SEARCH(D1,SRTs!B3:B1000)))

funny, I’m trying exactly the same thing, have you ever found a solution?

why this is invalid =FILTER(‘In – Out’!A2:A,’ In – Out’!C2:C=”In”) I hope you can help me, I followed the dot instructions in YT but no still I got this invalid message