Excel 2003利用SUMPRODUCT()來加總符合多條件的值

因為Excel 2003沒有sumifs()的函數,所以必須以其他的函數替代,這裡範例使用sumproduct()函數

如下圖,想要計算同一料號,同一廠商,同一卡號在不同時間收料的總數量。
所以在J欄的公式如畫面:

SUMPRODUCT(($A$2:$A$11=G2)*1,($B$2:$B$11=H2)*1,($C$2:$C$11=I2)*1,D2:D11)
公式的邏輯是先判斷單欄的資料是否有符合指定的值,並且三個欄位回傳的值必須同時為1,才傳回D欄的值,最後加總。

使用 SUMPRODUCT 函數中的每一個判斷結果(True 或 False),在乘以1之後,其結果為1或0。
(True X 1 ) * (True X 1 ) = 1 * 1 = 1 (True)
(True X 1 ) * (False X 1 ) = 1 * 0 = 0 (Fasle)
(False X 1 ) * (True X 1 ) = 0 * 1 = 0 ( Fasle)
(Fasle X 1 ) * (Fasle X 1 ) = 0 * 0 = 0 ( Fasle)

以第2列的資料(aa,aa01,aa02,10)與G2、H2、I2來比對為範例
($A$2:$A$11=G2)*1=1
($B$2:$B$11=H2)*1=1
($C$2:$C$11=I2)*1=1
因為三個條件都為1,所以傳回10

以第3列的資料(cc,cc01,cc02,30)與G2、H2、I2來比對為範例
($A$2:$A$11=G2)*1=0
($B$2:$B$11=H2)*1=0
($C$2:$C$11=I2)*1=0
只要有一個條件是0,就會傳回0

這樣就可以替代sumifs()函數了。


Oracle Data Gurad Archive Gap