Add cell on different sheet when the adjacent is equal to another cell-CodePudding

I have 2 sheet2 on the same file: the first collect the answers of different trial judge and the second should make the total Like this: First sheet

Name q1 q2 q3 total judge_id
Bob  1  5  8  14    1
Jeff 2  4  2  8     1
Bob  3  1  4  8     2
Bob  5  3  2  10    3
Jeff 6  1  8  15    3

Second sheet

judge_id  1  2  3  tot
Bob      14  8 15  37
Jeff      8  # 15  23

How can I sum only the row on a 'person' in particolar? There is a fast way to do it without open Google Script?

CodePudding user response：

try:

=ARRAYFORMULA({QUERY({A2:F}, 
 "select Col1,sum(Col5) where Col1 is not null group by Col1 
  pivot Col6 label Col1'judge_id'"), {"tot"; MMULT(QUERY(QUERY({A2:F}, 
 "select sum(Col5) where Col1 is not null group by Col1 pivot Col6"), 
 "offset 1", )*1, SEQUENCE(COUNTUNIQUE(F2:F), 1, 1, ))}})

CodePudding user response：

Here's my take on it:

={
QUERY(A1:F,"SELECT A, SUM(E) WHERE A IS NOT NULL GROUP BY A PIVOT F LABEL A 'Judge ID'"),
QUERY(A1:F,"SELECT SUM(E) WHERE A IS NOT NULL GROUP BY A")
}