SUMPRODUCT函數注意事項：乘號與逗號有區別

點擊「箭頭所指處」可快速關注

微信號：Excel-Word-PPT

問題來源

前段時間，韓老師講過用SUMPRODUCT函數分部門統計數據、處理分類排名、統計月份缺勤人數等用法，可以看到這個函數在處理「分類統計」問題上幾乎所向披靡，無所不能。

但，昨天，朋友在用SUMPRODUCT函數分部門、分產品統計銷量的時候，卻出現的問題。

韓老師總結如下：

問題分析

函數解釋：

SUMPRODUCT函數是Excel中的數學函數，用於在給定的幾組數組中，將數組間對應的元素相乘，並返回乘積之和。

其基本語法為：

SUMPRODUCT(array1,[array2], [array3], ...)

SUMPRODUCT函數語法具有下列參數：

Array1：必需。其相應元素需要進行相乘並求和的第一個數組參數。

Array2, array3,...：可選。2到255個數組參數，其相應元素需要進行相乘並求和。

特別注意：

數組參數必須具有相同的維數。否則，函數SUMPRODUCT將返回#VALUE!錯誤值#REF!。

本題中的解釋

本題中有三個數組，返回值分別是：

($A$2:$A$15=F2)；

($B$2:$B$15=G2)；

$C$2:$C$15：

本題中，三個公式中，每個數據之間的計算符號不一樣，要麼「*」，要麼「,」，符號不同，結果也不相同。

第一個公式：

H2單元格內的公式:

=SUMPRODUCT(($A$2:$A$15=F2),($B$2:$B$15=G2),$C$2:$C$15)

結果返回值是0，原因分析：

三個數組之間都是逗號","，返回零值的主要原因在於前兩個數組，這兩個數組返回的都是邏輯值，中間如果用","，互相獨立的兩個邏輯數據之間不能進行對應位置處邏輯值相乘。

而如果改成=SUMPRODUCT(($A$2:$A$15=F2)*1,($B$2:$B$15=G2)*1,$C$2:$C$15)，通過*1，先把邏輯值變成數值，中間再用","，對應位置處數據就可以相乘了。

第二個公式：

I2單元格內的公式:

=SUMPRODUCT(($A$2:$A$15=F2)*($B$2:$B$15=G2)*$C$2:$C$15)

結果返回值是#VALUE，原因分析：

錯誤的原因是第三個數組內有文本，文本不能直接參与求和。

第三個公式：

J2單元格內的公式:

=SUMPRODUCT(($A$2:$A$15=F2)*($B$2:$B$15=G2),$C$2:$C$15)

結果返回值是正確結果，原因分析：

前兩上數組之間對應位置的邏輯值相乘，轉變成數組

。

第三個數組$C$2:$C$15前加「,」，公式用逗號分隔，相乘區域中有文本，則當0處理。

總之

SUMPRODUCT多條件求和，寫成如下格式:

=SUMPRODUCT((條件一)*(條件二)*……*(條件N),求和範圍)

素材下載

鏈接：http://pan.baidu.com/s/1dEPZUe9

密碼：m5is

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！