Google Sheets nested query statements with different ranges

Question

I have two pivot tables with the same columns, but different filters

PT1 (P3:V) - shows all CR data PT2 (Y3:AF) - shows only RESC CR data

I have a page that summarizes this data in a "prettier format" and have a query in B5 that will query data based on what is selected in C2. I was able to get it to query all if they select all or by specific reviewer's names (both query PT1), using the following formula:

=IF(C2="All", QUERY({'Pivot Table 5'!P3:W1001}, "SELECT P, Q, S, T, U, V, W"), QUERY({'Pivot Table 5'!P3:W1001}, "SELECT P, Q, S, T, U, V, W WHERE P = '"&$C$2&"'"))

I am trying to add to the formula so that it queries only RESC CRs if "All RESC CRs" is selected in C2 (PT2).

Here is the formula I am trying to use:

=IFS(AND(C2="All"), QUERY({'Pivot Table 5'!P3:W1001}, "SELECT P, Q, S, T, U, V, W"), AND(C2="All RESC CRs"), QUERY({'Pivot Table 5'!Y3:AY1001}, "SELECT AR, AS, AU, AV, AW, AX, AY"), AND(C2<>"All", C2<>"All RESC CRs"), Query({'Pivot Table 5'!P3:W}, "SELECT P, Q, S, T, U, V, W WHERE P = '"&$C$2&"'"))

Here is the sample sheet: https://docs.google.com/spreadsheets/d/18SC_hQbWeFlYedLN5k0fuuwqgi8fdyW1A4MCfFTpVME/edit?usp=sharing

I'm assuming it is because the query range for the second query is different? Any help is greatly appreciated!

Answer 1

try:

=IF(C2="All",           QUERY('Pivot Table 5'!P3:W, "select P,Q,S,T,U,V,W"), 
 IF(C2="All RESC CRs"), QUERY('Pivot Table 5'!Y3:AY, "select AR,AS,AU,AV,AW,AX,AY"), 
                        QUERY('Pivot Table 5'!P3:W, "select P,Q,S,T,U,V,W where P = '"&C2&"'")))

---

update:

=IF(C2="All",          QUERY('Pivot Table 5'!P3:W, "select P,Q,S,T,U,V,W", ), 
 IF(C2="All RESC CRs", QUERY('Pivot Table 5'!P3:W, "select P,Q,S,T,U,V,W where R contains 'RESC'",),
                       QUERY('Pivot Table 5'!P3:W, "select P,Q,S,T,U,V,W where P = '"&C2&"'",)))