google-sheetsgoogle-sheets-formula

Sort Query Totals for rows in Google Sheets

I make pivot table by Query using this formula

=BYROW(
  BYCOL(Query({Query('2025'!$A:$AA,"Select C, H,Sum (N) 
where C is not null and S = True  and C >= date """&text(C2,"yyyy-mm-dd")&""" group by H,C order by Sum(N) desc")},"Select Col2, Sum(Col3) group by Col2 pivot Col1 "),LAMBDA(cv, {cv;IF(count(cv)=0,"TotByName", SUM(cv))}))
  ,LAMBDA(rv, {rv,IF(count(rv)=0,"TotByCol", SUM(rv))}))

The problem is I need to sort the total column desc, and I don't know how to do it because I didn't have the number of last column

Solution

  • Use let(), sort() and choosecols(), like this:

    =let(
  data, byrow(bycol(query(query(...,
  sort(data, choosecols(data, -1), false)
)

    See let(), sort() and choosecols().