Filter Google Sheet based on multiple checkboxes

Question

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

Answer 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

---

Query 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

Query Output

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