網友根據前一篇文章:Excel-計算多欄多列交集的小計(SMPRODUCT),如下圖,如果想要列入小計的欄列交集處包含了文字(例如:A),則原公式會發生錯誤,該如何調整?

例如:人員『寅』和項目『乙』資料的交集是『A,A,59,A,79,A,A,78,21,69』,其中包含了 3 個A是非數,該如何計算數字和?

Excel-計算多欄多列交集的小計(SUM,IFERROR,陣列公式)

 

【公式設計與解析】

儲存格M3:{=SUM(IFERROR(B2:J18*(B1:J1=M2)*(A2:A18=M1),0))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

原先的公式:

儲存格M3:=SUMPRODUCT(B2:J18*(B1:J1=M2)*(A2:A18=M1))

當公式中執行『*』運算時,會因文字(A)傳回 #VALUE,而發生錯誤。

新的公式改用陣列公式,並且利用 IFERROR 函數將錯誤訊息轉換為『0』,再自行利用 SUM 函數予以加總,即為所求。

如果你將 SUM 函數以 SUMPRODUCT 函數置換結果會一樣。

儲存格M3:{=SUMPRODUCT(IFERROR(B2:J18*(B1:J1=M2)*(A2:A18=M1),0))}

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(3) 人氣()