27

In Google Sheets, I want to filter view my results so only rows with an "x" in column D AND/OR column E are shown. If I filter to show the columns with an "x" in them, it will only show rows with an "x" in column D AND column E.

How do I make it do AND/OR? When I click the filter button in the column it just asks me to select which entries I want to show.

Screenshot

ZygD
  • 22,092
  • 39
  • 79
  • 102
Rage Kage
  • 377
  • 1
  • 3
  • 11
  • Drop the AND entirely. OR will work fine for D, E or D and E both. If column D contains 'X', it matches. If column E contains it, it matches. If both of them contain it, you have a match. (And *Thoughts?* is not a question here. You're liable to collect some downvotes as a result. See [ask].) – Ken White Nov 09 '17 at 16:47
  • I'm not sure how to get it to do that. I updated my question and added a screenshot. – Rage Kage Nov 09 '17 at 16:53

2 Answers2

34
  • Click on Column D.
  • On the menu click 'Data'.
  • Select 'Create a filter'.
  • Click the filter icon that appeared at the top of Column D.
  • Select 'Filter by condition'.
  • Click the 'None' box and scroll down to 'Custom formula is'.
  • Select and enter =OR(D:D="x",E:E="x") in value or formula.
  • Click OK.

Alternatively, you can create a new sheet and in A1 enter =FILTER(Sheet1!A:E,(Sheet1!D:D="x")+(Sheet1!E:E="x")). Adjust the columns as needed.

Slothie
  • 61
  • 9
Ed Nelson
  • 10,015
  • 2
  • 27
  • 29
  • 1
    Thank you! That first bit helped. I couldn't figure out the custom formula bit when I was trying. :) – Rage Kage Nov 10 '17 at 00:12
  • @EdNelson I have a similar question. I'd like to filter where column D is "x" (string) or column E is true (boolean). The following OR condition in the custom filter on only column D does _not_ work: `OR(D:D="x",E:E=TRUE)`. Any thoughts? – Khom Nazid Sep 27 '18 at 13:24
  • Try putting true in quotes (E:E="true") – Ed Nelson Sep 27 '18 at 17:02
  • 1
    @EdNelson - Thanks for this, but you addressed a FILTER, not a FILTER VIEW. The big difference is that a Filter affects all viewers, where a Filter View affects only the person viewing the Sheets document at that time. I THINK I can put the formula you mention into the "is formula" field of the column's filter view selection, but I need it to be "contains" and not "equal to," and to also address an adjacent column. Any help there? – David in Mississippi Jan 31 '19 at 13:34
  • @DavidinMississippi try =REGEXMATCH(E:E,"dribbling") – Kamilski81 Apr 08 '19 at 17:50
  • Thanks so much for this! I can confirm this works in filter views and even respects the range of the filter view `=OR(N:N>10%,O:O>10%,P:P>10%)` – Shanness Jan 03 '22 at 02:12
  • For more advanced scenarios, I found this article useful: https://infoinspired.com/google-docs/spreadsheet/how-to-use-and-or-with-google-sheets-filter-function-advanced-use/ – ottodidakt Jul 18 '22 at 06:14
2

you can also use QUERY formula like:

=QUERY(A:E; "where D='x' or E='x'"; 0)

=QUERY(A:E; "where D='x' and E='x'"; 0)

and to make it case insensitive you can use lower like:

=QUERY(A:E; "where lower(D)='x' or lower(E)='x'"; 0)

=QUERY(A:E; "where lower(D)='x' and lower(E)='x'"; 0)

in case you want to use alternating values for 1 column you can use:

=QUERY(A:E; "where D matches 'x|y'"; 0)
player0
  • 124,011
  • 12
  • 67
  • 124