How do I filter view two columns OR instead of AND?

user_5656175 0
Viewed 36099

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

google-query-language google-sheets-formula google-sheets filter
2 Answers
  • 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.

user_20404910

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)
user_56326290
Related