請問大大,
=SUMPRODUCT(COUNTIF(OFFSET($A$1:$D$1,ROW(1:3)-1,,,),$X$1),COUNTIF(OFFSET($A$2:$D$2,ROW(1:3)-1,,,),K$1)),其中除了要計算$X$1外,還要計算出$Y$1,$Z$1,W$1,總和即
=SUMPRODUCT(COUNTIF(OFFSET($A$1:$D$1,ROW(1:3)-1,,,),$X$1),COUNTIF(OFFSET($A$2:$D$2,ROW(1:3)-1,,,),K$1))+SUMPRODUCT(COUNTIF(OFFSET($A$1:$D$1,ROW(1:3)-1,,,),$Y$1),COUNTIF(OFFSET($A$2:$D$2,ROW(1:3)-1,,,),K$1))+SUMPRODUCT(COUNTIF(OFFSET($A$1:$D$1,ROW(1:3)-1,,,),$Z$1),COUNTIF(OFFSET($A$2:$D$2,ROW(1:3)-1,,,),K$1))+SUMPRODUCT(COUNTIF(OFFSET($A$1:$D$1,ROW(1:3)-1,,,),$W$1),COUNTIF(OFFSET($A$2:$D$2,ROW(1:3)-1,,,),K$1))
請教以上如何可以精簡成一個陣列程式,謝謝!
已邀請的邦友 {{ invite_list.length }}/5
iThome鐵人賽
看影片追技術