Excel一個SUMPRODUCT函數打遍COUNTIF, SUMIF, COUNTIFS, SUMIFS

SUMPRODUCT預設語法

=SUMPRODUCT (array1， [array2]， [array3]， …)

若只有兩陣列

會計算Array1的元素1*Array2的元素1 +

Array1的元素2*Array2的元素2 +

Array1的元素3*Array2的元素3

……

各陣列引數必須有相同的維度

 

不過也可以用來取代

COUNTIF, SUMIF函數

這時候的語法變成

SUMPRODUCT(條件1*條件2*條件3*……)

條件的格式如:

Range 運算子 Criteria

B2:B7=”儲蓄保險王”

 

本月業績如下圖,

請問”儲蓄保險王”

貢獻了幾次業績?

總共多少業績?

貢獻了幾次業績?

使用COUNTIF()

=COUNTIF(B2:B7,”儲蓄保險王”)

答案為3

改用SUMPRODUCT()

=SUMPRODUCT((B2:B7=”儲蓄保險王”)*1)

答案一樣為3

 

貢獻多少業績?

使用SUMIF函數

=SUMIF(B2:B7,”儲蓄保險王”,C2:C7)

答案為30000

改用SUMPRODUCT函數

=SUMPRODUCT((B2:B7=”儲蓄保險王”)*(C2:C7))

答案一樣是30000

 

既然SUMPRODUCT的工作

可以用COUNTIF, SUMIF取代

那還有什麼特別呢?

再繼續看下一個例子

一間迷你小學

有三個班級,

每班有三個學生

學生分數如下

校排名可以簡單用

RANK或RANK.EQ解答

=RANK.EQ(D2,$D$2:$D$10)

(Excel TQC考題406)

 

班排名呢?

=SUMPRODUCT(($C$2:$C$10=C2)*($D$2:$D$10>D2))+1

以下錯誤示範

=SUMPRODUCT(($C$2:$C$10=C2)*($D$2:$D$10>=D2))

本範例可以算出一樣的結果

但若出現同分狀況

例如:B,C學生同分70

正確公式會出現兩個第二名

但錯誤示範則出現兩個第三名

第二名從缺

 

這時候要用COUNTIFS替代的話

=COUNTIFS($C$2:$C$10,C2,$D$2:$D$10,“>75”)+1

“>75” , “>70” , “>65”

要用以下較不直覺的方式指定儲存格

才能拖曳

=COUNTIFS($E$2:$E$10,E2,$F$2:$F$10,“>”&F2)+1

“>F2″的話,無法拖曳

 

SUMPRODUCT(條件1*條件2*條件3*……)

沒有條件數目的限制

可以取代COUNTIFS, SUMIFS