Sort Data in Google Sheets

Question

I have a spreadsheet that records all the data in the worksheet named 'All responses' and another one in the worksheet named 'Leaderboard report' where it filters unique values in column A (NAME) and adds the value of its corresponding columns (C, D, E, F, G, H).

enter image description here enter image description here

In the Leaderboard report, there is a column called 'Total' that sums all the values from column C-H.

and I have these formulas from A2 - I2, respectively, to sum the values of the unique names.

=unique(tocol('All responses'!C2:C,1))
=unique(tocol('All responses'!D2:D,1))
=map($A2:$A,lambda(Σ,if(Σ="",,sumif('All responses'!$C:$C,Σ,'All responses'!F:F))))
=map($A2:$A,lambda(Σ,if(Σ="",,sumif('All responses'!$C:$C,Σ,'All responses'!H:H))))
=map($A2:$A,lambda(Σ,if(Σ="",,sumif('All responses'!$C:$C,Σ,'All responses'!I:I))))
=map($A2:$A,lambda(Σ,if(Σ="",,sumif('All responses'!$C:$C,Σ,'All responses'!J:J))))
=map($A2:$A,lambda(Σ,if(Σ="",,sumif('All responses'!$C:$C,Σ,'All responses'!L:L))))
=map($A2:$A,lambda(Σ,if(Σ="",,sumif('All responses'!$C:$C,Σ,'All responses'!N:N))))
=IF(A2="",,SUM(C2:H2))

https://docs.google.com/spreadsheets/d/1rL8KDZcYXNv-eJTiUW0EtAqTsrpHxn0vk6aitN0t02w/edit#gid=898926167

How do I sort the data on Leaderboard report worksheet using the results in the TOTAL as a reference in descending order? (given the formulas that I already setup in the worksheet)

I would appreciate your help!

Answer 1

You may use this single formula instead:

=let(Σ,query('All responses'!C2:N,"Select Col1,Col2,sum(Col4),sum(Col6),sum(Col7),sum(Col8),sum(Col10),sum(Col12) where Col1<>'' group by Col1,Col2"),
     sort(byrow(chooserows(Σ,sequence(rows(Σ)-1,1,2)),lambda(Λ,hstack(Λ,sum(choosecols(Λ,sequence(6,1,3)))))),9,1))

Answer 2

You can re-use your existing formulas with let as follows.

=let(names,unique(tocol('All responses'!C2:C,3))
    ,emails,unique(tocol('All responses'!D2:D,3))
    ,data,hstack(
     map(names,lambda(Σ,sumif('All responses'!$C:$C,Σ,'All responses'!F:F)))
    ,map(names,lambda(Σ,sumif('All responses'!$C:$C,Σ,'All responses'!H:H)))
    ,map(names,lambda(Σ,sumif('All responses'!$C:$C,Σ,'All responses'!I:I)))
    ,map(names,lambda(Σ,sumif('All responses'!$C:$C,Σ,'All responses'!J:J)))
    ,map(names,lambda(Σ,sumif('All responses'!$C:$C,Σ,'All responses'!L:L)))
    ,map(names,lambda(Σ,sumif('All responses'!$C:$C,Σ,'All responses'!N:N))))
    ,unsorted,hstack(names,emails,data,byrow(data,lambda(r,sum(r))))
    ,sort(unsorted,columns(unsorted),true))

(You could keep all the if(Σ="",, if you wish, although unnecessary.)

This should preserve all the formatting in the previous cells. However, it is not the most computationally efficient method because each additional sumif call repeats the same comparisons, even though it sums a different column.