I have raw data in B3:C11. In Column F, I would like to sum up the number in Column C for each person, then sort the table by the sum.
Could anyone find only one dynamic array formula for E3 to achieve this (without borrowing intermediate cells)?
3 Answers
Little bit stress but achievable.
=INDEX(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,SORTBY(UNIQUE(B3:B11),SUMIF(B3:B11,UNIQUE(B3:B11),C3:C11),-1),SORT(SUMIF(B3:B11,UNIQUE(B3:B11),C3:C11),,-1))&"</s></t>","//s"),TRANSPOSE(SEQUENCE(COUNTA(UNIQUE(B3:B11))/2,COUNTA(UNIQUE(B3:B11)))))
Different approach using LET:
=LET(x,FILTER(B:B,B:B<>""),y,UNIQUE(x),SORT(IF(SEQUENCE(1,2)<=COLUMNS(x),y,SUMIF(B:B,INDEX(y,SEQUENCE(COUNTA(y))),C:C)),2,-1))
Don't know if this counts as only one, but you could do
=SORTBY(UNIQUE($B$3:$B$11), SUMIF($B$3:$B$11, UNIQUE($B$3:$B$11), $C$3:$C$11),-1) in E3 and then SUMIF those values in F3.
Related