Excel的SUMPRODUCT函數功能強大,網上有許多文章介紹,此處不再贅述。以SUMPRODUCT來做小計的功能,網上未見相關資料,今介紹於下。

SUMPRODUCT是行列式函數,它的參數是行列式,可有三十個之多,基本用法如下:

=SUMPRODUCT({1,2,3},{4,5,6},{7,8,9})

這個函數展開來是:

=1X4X7+2X5X8+3X6X9=270

我見到這個函數,先在心中將之轉為直式,成了:

1 4 7
2 5 8
3 6 9

然後加上乘號,變成:

1X4X7
2X5X8
3X6X9

當然,用橫式也行:

1 2 3
4 5 6
7 8 9

乘號加於垂直方向,垂直相乘:

1 2 3
X X X
4 5 6
X X X
7 8 9

基本功能介紹完畢後，來看上圖。圖中貨物按打數排列，總打數等於箱數乘以打數。若要算甲乙的總共打數，用SUMPRODUCT很容易就算出來：

＝SUMPRODUCT(B2:B6,C2:C6)=75

然若要算三打裝的共幾打、四打裝的共幾打，等等，就比較複雜。此時要加兩個篩子，把不要的資料篩掉。一要把不同打數的箱子篩掉，一要把同打數的箱子，但不是最後一箱的篩掉。

把不同打數的箱子篩掉，式子是：

--($B$2:$B$6=B2)

把這個行列式展開,得下式:

$B$2=B2    TRUE    1
$B$3=B2    FALSE    0
$B$4=B2    FALSE    0
$B$5=B2    FALSE    0
$B$6=B2    FALSE    0

對照上圖,得知除了第一式為真外,其餘皆為偽。前端的--把真偽換成數字1與0，真為1，偽為0。

把--($B$2:$B$6=B2)放入SUMPRODUCT中，得：

=SUMPRODUCT(--($B$2:$B$6=B2),$C$2:$C$6,($B$2:$B$6))

將這個行列式乘以其他的行列式，即是將打數不等於B2的全部濾掉，僅得最上面一列:

1X3X1
0X4X6
0X4X9
0X4X1
0X4X2

總和為3。

再來看下面一列，即第三列：

--($B$2:$B$6=B3)

把這個行列式展開,得下式:

$B$2=B3    FALSE    0
$B$3=B3    TRUE    1
$B$4=B3    TRUE    1
$B$5=B3    TRUE    1
$B$6=B3    TRUE    1

將這個行列式乘以其他的行列式，即是將打數不等於B3的全部濾掉:

0X3X1
1X4X6
1X4X9
1X4X1
1X4X2

得24+36+4+8，總和為72。第三至六列，結果完全一樣，都是72。

至此，H行的結果為：

3
72
72
72
72

這很接近欲得之結果，剩下來要把前三個72篩掉，僅留最後一個，式子是：

COUNTIF($B$2:$B$6,B2)=COUNTIF($B$2:B2,B2)

COUNTIF($B$2:$B$6,B2)算的是在$B$2:$B$6中，總共有幾個值等於B2，由上圖可知,三打裝的只有一種。

COUNTIF($B$2:B2,B2)算的是從$B$2到B2中，有幾個B2，這當然只有一個。這個式子看不出什麼名堂，下面幾式，便知分曉：

COUNTIF($B$2:$B$6,B3)=COUNTIF($B$2:$B$6,B4)=COUNTIF($B$2:$B$6,B5)=COUNTIF($B$2:$B$6,B6)=4
COUNTIF($B$2:B3,B3)=1
COUNTIF($B$2:B4,B4)=2
COUNTIF($B$2:B5,B5)=3
COUNTIF($B$2:B6,B6)=4

COUNTIF($B$2:$B$6,B2)=COUNTIF($B$2:B2,B2)    1=1    真
COUNTIF($B$2:$B$6,B3)=COUNTIF($B$2:B3,B3)    4=1    偽
COUNTIF($B$2:$B$6,B4)=COUNTIF($B$2:B4,B4)    4=2    偽
COUNTIF($B$2:$B$6,B5)=COUNTIF($B$2:B5,B5)    4=3    偽
COUNTIF($B$2:$B$6,B6)=COUNTIF($B$2:B6,B6)    4=4    真

如此一來,前三個72為偽,也被濾掉，只餘第一列三打裝及最後一列四打裝為真。這正是我們想要的結果。

把這個式子代入原式子,得:

=SUMPRODUCT(--($B$2:$B$6=B2),$C$2:$C$6,($B$2:$B$6)*(COUNTIF($B$2:$B$6,B2)=COUNTIF($B$2:B2,B2)))


此即為我們要的式子。