Add another criteria to a Google Sheet formula

Question

I have this formula

=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,0))

in my Leaderboard report worksheet to get the unique values of Column C (Name) in All responses worksheet and adds its corresponding column values (Column F, H I, J, L, N) for that unique name. It also calculates the total for that unique row and sorts it in descending order.

I need help modifying the formula to add another criterion. I want to get the same result for the unique rows but for each week (All responses, column A) in Week 1 worksheet.

Assuming the formula, the result should be (in Week 1 worksheet):

Angelica Test 3, 1, 24, 49, 2, 2, and the total of 81
Angelica Viajedor 0, 1, 5, 10, 0, 1 and the total of 17
Dana DeVito 0, 1, 3, 9, 0, 1 and the total of 12
Lynn Ann Kent 0, 0, 2, 3, 0, 1 and the total of 6
Tim Muse 0, 1, 2, 1, 0, 0 and the total of 3

in that specific order.

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

Answer 1

I updated the doc with a new tab titled "[WEEK 1] Copy of Leaderboard Report 1" after adjusting the original formula in the Leaderboard Report to reflect just Week 1's totals. I just adjusted the original data range, reworked the sum formula, and then added a filter to the first column (Week) - "where Col1=1." Hope this is what you were looking for.

Formula:

=sort(query('All responses'!A2:N,"Select Col3,Col4,sum(Col6),sum(Col8),sum(Col9),sum(Col10),sum(Col12),sum(Col14),sum(Col6)+sum(Col8)+sum(Col9)+sum(Col10)+sum(Col12)+sum(Col14) where Col1=1 group by Col3,Col4 label sum(Col6) '',sum(Col8) '',sum(Col9) '',sum(Col10) '',sum(Col12) '',sum(Col14) '', sum(Col6)+sum(Col8)+sum(Col9)+sum(Col10)+sum(Col12)+sum(Col14) ''",0),9,FALSE)