合併格分別加總 (難度高) (11911)

先刪G4:K4跨欄置中 公式下G4  RUN後 在合併G4:K4 右拉  {=INDEX($C:$C,MIN(IF(COUNTIF($F4:F4,$C5:$C15)=0,ROW(5:15),99)))&""

G6:U6合併格公式 {=IF(OFFSET($G4,,INT(COLUMN(A1)/5)*5)="","",SUMPRODUCT((LOOKUP(1,0/($G4:G4<>""),$G4:G4)=$C5:$C15)*OFFSET($D5:$D15,,MAX(MOD(COUNTA($G5:G5)+1,2)))))

合併格公式 :公式下G6-->ENTER-->圈選G6:U6-->游標放公式尾-->ctrl+enter二鍵一起按

(上面空白=0  改為空)G5{=TEXT(SUMPRODUCT(($C5:$C21=LOOKUP("礱",$G4:G4))*ISNUMBER(FIND(RIGHT(LOOKUP("礱",$G5:G5),2),$D4:$E4))*$D5:$E21),"0;;")

(上面空白 改為空)G5{=IF(OFFSET($G4,,INT(COLUMN(A1)/5)*5)="","",SUMIF($C5:$C21,LOOKUP("礱",$G4:G4),OFFSET($D5,,MOD(COUNTA($G5:G5)-1,2),17)))