Google Sheets: How do I filter view two columns OR instead of AND

filter google-sheets google-sheets-formula google-sheets-query google-query-language
Rage Kage picture Rage Kage · Nov 9, 2017 · Viewed 14.1k times · Source

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

Answer

Ed Nelson picture Ed Nelson · Nov 10, 2017

Click on column D. On the menu click Data. Select Filter. Then click the filter icon. Select Filter by condition. Click the down arrow on none and scroll down to Custom formula is. Select and enter =or(D:D="x",E:E="x") in value or formula.Click OK. Or, create sheet2 and in A1 enter =FILTER(Sheet1!A:E,(Sheet1!D:D="x")+(Sheet1!E:E="x")). Adjust the columns as needed.

Related questions

Filter a range by array

I have a Google Spreadsheet containing the teams of the UEFA EURO 2012, and their scores: Team Points Goals scored ------ ------ ------------ Germany 6 3 Croatia 3 3 Ireland 0 1 ... ... ... Now I want to filter that list, so that the result contains only a subset …

Read More
How to filter one list of items from another list of items?

I have a huge list of items in Column A (1,000 items) and a smaller list of items in Column B (510 items). I want to put a formula in Column C to show only the Column A items not in Column …

Read More
How to sort only those rows which have no blank cell?

I have a Google Spreadsheet with two separate sheets. The first one is just a big list of names and data, and the second is supposed to be a sorted listing of all the data on the first sheet (sorted …

Read More