How to sort multiple columns using QUERY function together with checkboxes

Question

I need to sort multiple columns using the QUERY function together with checkboxes which indicate which columns to sort. This should work with multiple columns.

This is the formula:

=IFERROR(ARRAYFORMULA(
    QUERY(
        {AI4:AY101, TRIM(FLATTEN(QUERY(TRANSPOSE(AJ4:AY101),,9^9)))}, 
        "Select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17
        where Col18 is not null
        order by 
        Col"&$P$1&" "&$P$3&"
        ", )),"Loading…")

It works for single columns if I check only 1 checkbox. Is there a way to make it sort multiple columns if I check multiple checkboxes?

Demo file:
https://docs.google.com/spreadsheets/d/1x-IkvUr8hEoBwkKaoZWfdITSf4WXwFCgROpIEwOkaOI/edit

user_5632629 · Answer

try:

=ARRAYFORMULA(QUERY(
 {AI4:AY101, TRIM(FLATTEN(QUERY(TRANSPOSE(AJ4:AY101),,9^9)))}, 
 "select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17
  where Col18 is not null "&IF(SUM(C2:O2*1)>0,
  "order by "&TEXTJOIN(", ", 1, "Col"&MATCH(FILTER(C3:O3, C2:O2=TRUE), AI3:AY3, 0)&" "&
  IF(D1="descending", "desc", )), ), ))

How to sort multiple columns using QUERY function together with checkboxes

1 Answers