0

I have a Google Sheet with multiple columns filled with checkboxes with each row being for a specific product, above each column is another checkbox and the idea is to use these top checkboxes to create a filtered list of what is displayed. So if the top (filter) checkbox in column C is selected only items in that column that are checked will display but it will use an AND statement for any other 'filter' checkboxes selected in the rest of that row.

Ideally it would be the main data of the spreadsheet that gets filtered but happy for the filtered output to appear on a different sheet, if this is the case then it would be ideal to have those filter checkboxes on that separate sheet.

I'm trying to create a filter system where the checkboxes shown on the screenshot will filter out any rows under it that don't also have the checkbox in that column checked. This would allow multiple checkboxes to work as an AND operation. For example if "Spring" & "Day" were selected in the below screenshot all rows under it with the "Spring" & "Day" checkbox would be all that was displayed. If the filter output had to be on a different sheet that would also be fine.

What I've tried so far is using IF statements like =IF((C3=true),FILTER(A4:B274,C4:C274=TRUE), "No Filters Selected") and tried joining them together with AND with no success. I've also tried the following with little success

=IFERROR(QUERY(A4:B, "where "&TEXTJOIN(" and ", 1, 
 IF(C3=TRUE, "C=TRUE", ), 
 IF(D3=TRUE, "D=TRUE", )), 1), QUERY(A4:B))

enter image description here

2
  • Hi and welcome. Neil, do you have a specific question? What have you already tried to meet your expectations?
    – Tedinoz
    Sep 20, 2021 at 1:30
  • @Tedinoz Sorry if it wasn't clear, I'll just edit the question to clarity now.
    – Neil Nand
    Sep 20, 2021 at 1:33

1 Answer 1

1

Try this formula: =query({A3:I},"select * where (Col3 = true or Col4 = true or Col5 = true or Col6 = true or Col7 = true or Col8 = true or Col9 = true)")

Since you have a finite number of conditions (4 seasons and 2 times of day, it is not an onerous task to build a Query Select where clause that tests for any combination of those conditions.


Sample Data

sample


Query Output

output


VARIATION (Select report parameters)

Formula: =query({A3:I6},"select Col1, Col2, Col9 where 0=0"&IF($C$2," and Col3=TRUE","")&IF($D$2," and Col4=TRUE","")&IF($E$2," and Col5=TRUE","")&IF($F$2," and Col6=TRUE","")&IF($G$2," and Col7=TRUE","")&IF($H$2," and Col8=TRUE",""),1)


Sample Data

SAMPLE2

Query Output OUTPUT2

ht: @player0 Dynamically query or filter a table in Google Sheets using checkboxes and searchbox

2
  • Thank you very much, I used that second query with only a few minor amends to select the relevant columns in my spreadsheet and I found the header parameter for query needed to be set to 0 otherwise it didn't seem to filter down to anything other than the first row when its relevant values were true, otherwise it would show all rows (only had two rows of test data when I tried this).
    – Neil Nand
    Sep 20, 2021 at 11:08
  • Here's the query I've used: =query({A4:I6},"select Col1, Col2, Col9 where 0=0"&IF($C$3," and Col3=TRUE","")&IF($D$3," and Col4=TRUE","")&IF($E$3," and Col5=TRUE","")&IF($F$3," and Col6=TRUE","")&IF($G$3," and Col7=TRUE","")&IF($H$3," and Col8=TRUE",""),0)
    – Neil Nand
    Sep 20, 2021 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.