排序 依各部門總數值 (13912)

E2{=IFERROR(INDEX(B:B,RIGHT(LARGE(IF(MATCH(B$2:B$16,B:B,)=ROW($2:$16),MMULT(N(B$2:B$16=TRANSPOSE(B$2:B$16)),C$2:C$16)/1%+ROW($2:$16)),ROW(A1)),2)),"")

F2=IF(E2="","",SUMIF(B:B,E2,C:C))

另解{=IFERROR(INDEX(B:B,RIGHT(LARGE(IF(MATCH(B$2:B$16,B:B,)=ROW($2:$16),SUMIF(B:B,B$2:B$16,C:C)/1%+ROW($2:$16)),ROW(A1)),2)),"")



留言

  1. E2=IFERROR(INDEX(B:B,RIGHT(LARGE(IF(MATCH(B$2:B$16,B:B,)=ROW($2:$16),SUMIF(B:B,B$2:B$16,C$2:C$16)/1%+ROW($2:$16)),ROW(A1)),2)),"")

    MMULT那段也是用SUMIF表達就好

    回覆刪除
    回覆
    1. GOOD SUMIF(B:B,B$2:B$16,C:C) 改成 C:C 試試

      刪除

張貼留言