/docs/community?hl=en
/docs/community?hl=en
12/30/09
Original Poster
chrislennon

How to filter data based on OR condition

If I have two columns A and B and I want to return all rows from these columns where the values in A are greater than 5 OR the values in B are greater than 5 how do I achieve this?

Using the FILTER function, the conditions are AND conditions.  I have tried Filter(data,or(condition,condition)) but I get mis-matched range lengths error.  I have also tried using Filter(data,condition + condition) but this returns no results.

My apologies if the solution has already been described, I have spent some time searching but cannot locate the solution here
Community content may not be verified or up-to-date. Learn more.
All Replies (8)
Google user
12/30/09
Google user
Hi there.
Assuming that your data start at row one, you can try the following formula:
 
=filter(A1:B;(A1:A>5)+(B1:B>5))
 
If your data are in a sheet named Sheet1, you can use the filter formula in another sheet of the same worksheet, as in:
 
=filter(Sheet1!A1:B;(Sheet1!A1:A>5)+(Sheet1!B1:B>5))
 
Best regards,
Otávio
12/30/09
Original Poster
chrislennon
Thank you. I had the syntax of the brackets wrong
12/30/09
Original Poster
chrislennon
OK, I actually find that I need to add an AND condition to the criteria.  So the example becomes:
return all rows where values in column A are greater than 5 AND ALSO less than 10 OR the values in B are greater than 5

=filter(A1:B;(A1:A>5,A1:A<10)+(B1:B>5))

generates a parser error.  I have tried various combination of brackets.  How to achieve this?

AD:AM
12/30/09
AD:AM
<How to achieve this?>

=FILTER( A1:B ; ( ( A1:A > 5) * ( A1:A < 10 ) ) + ( B1:B > 5 ) )

ahab
12/30/09
ahab
In general in array expressions the Boolean AND can be replaced by multiplication (*) and the Boolean OR by addition (+). The Boolean NOT function can be used without problems in array expressions.
 
Note that 0 (zero) in Google Docs is equivalent to FALSE and that any non-zero value (also negative numbers!) are equivalent to TRUE. This means that when array expressions return values that have to be logically combined with other array values one sometimes has to simplify the result using ABS and SIGN e.g.
SIGN(ABS( <math array expression returning either 0 or non-zero>))+SIGN(ABS( <math array expression returning either 0 or non-zero>))
 
Also note that when the FIND or SEARCH functons are used in such array expressions it is best to wrap the FIND or SEARCH in an IFERROR which returns 0 on error, e.g.
IFERROR( FIND( "foo" ; <range> ) ; 0) + IFERROR( SEARCH( "bar" ; <range> ) ; 0)  
 
When array expression are used outside functions that interanally enable array expressions (like FILTER does, but only for the second and next arguments!) an ArrayFormula( ... ) wrap is required.
 
 
mlauer
6/10/10
mlauer
I am trying to switch from excel to g-docs.  One of the features I use a lot in excel is data > filter > autofilter. This auto pulls a list of possibles in that column.  What I usually do is activate for the top row in that sheet and then use the pull down to then filter one column at at time based on a single criteria per column.  Any suggestions would be appreciated. 
ahab
6/10/10
ahab
AutoFilter and its associated formula use is not supported in Google Docs spreadsheets.

There is some similarity to AutoFilter selection found in the list view: View->List view
bdurbin
7/6/10
bdurbin
hi @MLAUER

I found a way to do the Whole Auto Filter thing. 

I have a phone list on a sheet with A through J Columns (Branch, Last, First, Phone, Extension, Email, Fax, Mobile, Position, Notes)
then I have the filter on the next sheet. 

Row A is the same as the first sheet
Row B is my values to enter for the auto filter
Row C is blank to create whitespace
Cell D1 has the following formula:

=QUERY('Phone Log'!A3:J, "SELECT * WHERE A contains " & "'" & A2 & "'" & " AND B contains " & "'" & B2 & "'" & " AND C contains " & "'" & C2 & "'" & " AND D contains " & "'" & D2 & "'" & " AND E contains " & "'" & E2 & "'" & " AND F contains " & "'" & F2 & "'" & " AND G contains " & "'" & G2 & "'" & " AND H contains " & "'" & H2 & "'" & " AND I contains " & "'" & I2 & "'" & " AND J contains " & "'" & J2 & "'", 0)

Now when I type any characters in any of the cells in B2 through J2...the results appear below!

Now you can get fancier and add 2 or three rows (let's say: B, C, D) that can all filter. then you enter the formula like this:

=QUERY('Phone Log'!A3:J, "SELECT * WHERE (A contains " & "'" & A2 & "'" & " AND B contains " & "'" & B2 & "'" & " AND C contains " & "'" & C2 & "'" & " AND D contains " & "'" & D2 & "'" & " AND E contains " & "'" & E2 & "'" & " AND F contains " & "'" & F2 & "'" & " AND G contains " & "'" & G2 & "'" & " AND H contains " & "'" & H2 & "'" & " AND I contains " & "'" & I2 & "'" & " AND J contains " & "'" & J2 & "') OR (A contains " & "'" & A3 & "'" & " AND B contains " & "'" & B3 & "'" & " AND C contains " & "'" & C3 & "'" & " AND D contains " & "'" & D3 & "'" & " AND E contains " & "'" & E3 & "'" & " AND F contains " & "'" & F3 & "'" & " AND G contains " & "'" & G3 & "'" & " AND H contains " & "'" & H3 & "'" & " AND I contains " & "'" & I3 & "'" & " AND J contains " & "'" & J3 & "') OR (A contains " & "'" & A4 & "'" & " AND B contains " & "'" & B4 & "'" & " AND C contains " & "'" & C4 & "'" & " AND D contains " & "'" & D4 & "'" & " AND E contains " & "'" & E4 & "'" & " AND F contains " & "'" & F4 & "'" & " AND G contains " & "'" & G4 & "'" & " AND H contains " & "'" & H4 & "'" & " AND I contains " & "'" & I4 & "'" & " AND J contains " & "'" & J4 & "')", 0)

But...the problem is that because I use CONTAINS...the blanks in an empty row include all...so if you use 3 rows for filtering, you need to type something into all three rows (even if it is the same value)
I'm sure I can add an exclusion "IF BLANK", but I got lazy

Were these replies helpful?
How can we improve them?
 
This question is locked and replying has been disabled. Still have questions? Ask the Help Community.

Badges

Some community members might have badges that indicate their identity or level of participation in a community.

 
Expert - Google Employee — Googler guides and community managers
 
Expert - Community Specialist — Google partners who share their expertise
 
Expert - Gold — Trusted members who are knowledgeable and active contributors
 
Expert - Platinum — Seasoned members who contribute beyond providing help through mentoring, creating content, and more
 
Expert - Alumni — Past members who are no longer active, but were previously recognized for their helpfulness
 
Expert - Silver — New members who are developing their product knowledge
Community content may not be verified or up-to-date. Learn more.

Levels

Member levels indicate a user's level of participation in a forum. The greater the participation, the higher the level. Everyone starts at level 1 and can rise to level 10. These activities can increase your level in a forum:

  • Post an answer.
  • Having your answer selected as the best answer.
  • Having your post rated as helpful.
  • Vote up a post.
  • Correctly mark a topic or post as abuse.

Having a post marked and removed as abuse will slow a user's advance in levels.

View profile in forum?

To view this member's profile, you need to leave the current Help page.

Report abuse in forum?

This comment originated in the Google Product Forum. To report abuse, you need to leave the current Help page.

Reply in forum?

This comment originated in the Google Product Forum. To reply, you need to leave the current Help page.