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

Question

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.

Solution 1

• 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.

Solution 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)

Comments

[Ken White]

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 How to Ask.)

[Rage Kage]

I'm not sure how to get it to do that. I updated my question and added a screenshot.

[Rage Kage]

Thank you! That first bit helped. I couldn't figure out the custom formula bit when I was trying. :)

[Khom Nazid]

@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?

[Ed Nelson]

Try putting true in quotes (E:E="true")

[David in Mississippi]

@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?

[Kamilski81]

@DavidinMississippi try =REGEXMATCH(E:E,"dribbling")

[Commander Tvis]

Where such a formula should be placed? To Custom formula field in a filter view?

[player0]

@CommanderTvis no, these ones are for cell input

[Shanness]

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%)