Google Sheets QUERY with WHERE on multiple columns at the same time-CodePudding

Following this post Google Sheets QUERY with WHERE on multiple columns I build up my formula to select specific values from more columns but when I had a condition data start to be confused and not equal to what I selected. I started from this query which works perfectly on one column for each IF:

=QUERY(Concerti!A1:AL; "SELECT * WHERE 1=1" &IF(A2="TUTTI";"";" AND E = '"&A2&"' ") &IF(E2="TUTTI";"";" AND H = '"&E2&"' ") &IF(F2="TUTTI";"";" AND B = "&F2&" "); 1)

Then I wrote this query to select (and filter) the content in A4, E4 and F4 but it doesn't work correctly i.e. it keeps some data which doesn't exactly match with the values on A4, E4 and F4.

=QUERY(Concerti!A1:AL; "SELECT * WHERE 1=1" &IF(A2="TUTTI";"";" AND E = '"&A2&"' ") &IF(E2="TUTTI";"";" AND H = '"&E2&"' ") &IF(F2="TUTTI";"";" AND B = "&F2&" ") &IF(A4="TUTTI";"";" AND I = ('"&A$4&"') OR (K='"&A$4&"' OR M='"&A$4&"' OR O='"&A$4&"' OR Q='"&A$4&"' OR S='"&A$4&"') ") &IF(E4="TUTTI";"";" AND J = ('"&E$4&"') OR (L='"&E$4&"' OR N='"&E$4&"'    OR P='"&E$4&"' OR R='"&E$4&"' OR T='"&E$4&"') ") &IF(F4="TUTTI";"";" AND Y = ('"&F$4&"') OR (AA='"&F$4&"' OR AC='"&F$4&"'    OR AE='"&F$4&"' OR AG='"&F$4&"' OR AI='"&E$4&"') "); 1)

Where is my mistake? Thank you so much in advance!!! I post a screenshot of the project:

[![Filtering mask](https://i.stack.imgur.com/cRFLv.png)](https://i.stack.imgur.com/cRFLv.png)

CodePudding user response：

try:

=QUERY(Concerti!A1:AL; "where 1=1"&
 IF(A2="TUTTI";;" and  E = '"&A2&"'")&
 IF(E2="TUTTI";;" and  H = '"&E2&"'")&
 IF(F2="TUTTI";;" and  B =  "&F2&" ")&
 IF(A4="TUTTI";;" and (I = '"&A$4&"' 
                    or K = '"&A$4&"' 
                    or M = '"&A$4&"' 
                    or O = '"&A$4&"' 
                    or Q = '"&A$4&"' 
                    or S = '"&A$4&"')")&
 IF(E4="TUTTI";;" and (J = '"&E$4&"' 
                    or L = '"&E$4&"' 
                    or N = '"&E$4&"'    
                    or P = '"&E$4&"' 
                    or R = '"&E$4&"' 
                    or T = '"&E$4&"')")&
 IF(F4="TUTTI";;" and (Y = '"&F$4&"'
                    or AA= '"&F$4&"' 
                    or AC= '"&F$4&"'    
                    or AE= '"&F$4&"' 
                    or AG= '"&F$4&"' 
                    or AI= '"&E$4&"')"); 1)