今天給大家分享一個很常用、很實用、很強大、也很有意思的函數，Sumproduct。衆所周知，條件計數和求和是數據的統計和分析中經常要用到的2種技巧，而Sumproduct函數不但集合了條件計數、求和功能，還可以用于複雜情景下的排名處理等情景，所以有人說，精通Sumproduct函數，就可以打下Excel的半壁江山。

一、功能及語法結構。

功能：返回相應的數組或區域乘積的和。

語法結構：=Sumproduct(數組1,[數組2],[數組3]……)。

解讀：

1、我們可以将函數名稱Sumproduct分為2部分，Sum和Product，Sum是求和的意思，Product是乘積的意思，參數之間先乘積再求和。

2、從語法結構可以看出，Sumproduct函數本身默認執行數組運算。

3、Sumproduct函數會将參數中非數值類型的數組元素作為0處理。

4、各參數的維度必須相同，否則會返回錯誤值。

二、應用案例。

1、計算總銷售額。

方法：

在目标單元格中輸入公式：=SUMPRODUCT(D3:D12,E3:E12)。

解讀：

1、如上圖，要計算商品的總銷售額，一般思路為首先計算出每個商品的銷售額，然後求和。也就是需要2步來完成。

2、而Sumproduct函數将上述的兩個步驟合二為一，公式=SUMPRODUCT(D3:D12,E3:E12)的計算過程為：D3*E3 D4*E4……D12*E12，也就是每個數組對應的元素相乘，再求和。

3、部分親可能也遇到過将公式寫成：=SUMPRODUCT(D3:D12*E3:E12)的情況，同樣能夠得到正确的結果。Why？我們接着往下學習。

2、計算總銷售額。

在計算之前，我們先将數據表稍作改動，如下圖：

從圖中可以看出，商品“打印機”的銷量“待統計”，此時用公式：=SUMPRODUCT(D3:D12*E3:E12)來計算，結果如下圖：

從結果中可以看出，返回了錯誤值，Why？

分析數據源，不難發現，原因在于“打印機”的“銷量”為“待統計”，為文本值，而文本值是無法直接參與數據運算的，所以D5*E5返回錯誤值，進而導緻整個公式返回錯誤值。但如果将公式修改為：=SUMPRODUCT(D3:D12,E3:E12)，結果如下圖：

結果沒有任何問題，是正确的。

原因在于Sumproduct函數會将參數中非數值類型的數組元素作為0處理。

綜上所述，敲一下黑闆：

當Sumproduct函數的參數為數值類型時，公式=Sumproduct(數組1,[數組2],[數組3]……)或=Sumproduct(數組1*[數組2]*[數組3]……)均可用于計算；而參數中含有非數值類型時，隻能用=Sumproduct(數組1,[數組2],[數組3]……)進行計算。

3、參數的維度必須相同。

目的：計算商品的總銷售額。

在目标單元格中輸入公式：=SUMPRODUCT(D3:D12,E3:E10)，結果如下圖：

結果返回錯誤值，從公式中不難發現，公式中的兩個元素的維度不同，E3:E10明顯比D3:D12少了2個元素，D3和E3結對，D4和E4結對……D11以及D12和誰結對呢？一個蘿蔔一個坑，D11和D12有蘿蔔沒坑，所以就返回了錯誤值。

4、單條件計數。

如下圖：

現在要計算員工中相應“學曆”的人數，如果用Countif函數計算，公式為：=COUNTIF(F3:F24,J3)，但如果用Sumproduct去計算，怎麼操作呢？

從示例圖中可以看出，公式為：=SUMPRODUCT((F3:F24=J3)*1)；先判斷F3:F24=J3是否成立，如果成立，返回TRUE，否則返回FALSE，由此建立一個由邏輯值組成的數組，前文中已經講過，Sumproduct函數會将非數值型的數組元素作為0處理，邏輯值自然屬于非數值型的數組元素，為了避免Sumproduct函數将邏輯值視為0，造成統計錯誤，所以乘以輔助值1，把邏輯值轉換為數值類型的值，最後統計求和，得到計數結果。

5、單條件求和。

目的：統計相應“學曆”人員的總“月薪”。

單條件求和，應該是Sumif函數的本職工作，公式為：=SUMIF(F3:F24,J3,G3:G24)，其實除了用Sumif函數之外，用Sumproduct函數也可以實現。

方法：

在目标單元格中輸入公式：=SUMPRODUCT((F3:F24=J3)*G3:G24)。

解讀：

1、首先判斷F3:F24=J3是否成立，建立一個由邏輯值組成的數組，然後和G3:G24元素中相應的值乘積，再求和。

2、如果此處将公式更改為：=SUMPRODUCT(F3:F24=J3,G3:G24)，能否得到想要的結果呢？大家可以思考一下，将自己的意見發表到留言區或者私信和小編讨論交流哦！

6、多條件計數、求和。

目的：按“性别”統計相應“學曆”的人數和總“月薪”。

按“性别”統計相應“學曆”的人數，這是一個多條件計數的問題，如果用Countifs函數去計算，公式為：=COUNTIFS(D3:D24,J3,F3:F24,K3)，參數相對來說較多，有點兒簡單問題複雜化的感覺，所以除了用Countifs函數外，還可以用Sumproduct函數。

公式為：=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3))。

除了計數之外，還要按“性别”統計相應“學曆”的總“月薪”，就屬于求和的問題，而且是多條件求和，公式為：=SUMIFS(G3:G24,D3:D24,J3,F3:F24,K3)；也有點兒簡單問題複雜化，如果用Sumproduct函數來實現：

公式為：=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3)*G3:G24)。

也可以是：=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3),G3:G24)，Why？留言區或私信告訴小編哦！

應用拓展：

如果老闆要知道“男”同志或“女”同志“大本”、“大專”、“職高”的總人數和總“月薪”，該如何操作呢？

通過上文的學習，相信大家已經能夠解決這個問題，就是将公式進行疊加。

計數公式為：=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3)) SUMPRODUCT((D3:D24=J3)*(F3:F24=K4)) SUMPRODUCT((D3:D24=J3)*(F3:F24=K5))。

求和公式為：=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3)*G3:G24) SUMPRODUCT((D3:D24=J3)*(F3:F24=K4)*G3:G24) SUMPRODUCT((D3:D24=J3)*(F3:F24=K5)*G3:G24)

貌似沒有任何問題，但仔細分析，如果有20個學曆等次或其他等次，公式要重複20次，手累就不談了，萬一寫錯，………………是不是很麻煩？看小編是如何處理的？

計數公式為：=SUMPRODUCT((D3:D24=J3)*(F3:F24={"大本","大專","職高"}))。

求和公式為：=SUMPRODUCT((D3:D24=J3)*(F3:F24={"大本","大專","職高"})*G3:G24)。

是不是清晰了很多，隻需要給大括号中添加條件即可。

解讀：

1、多條件計數時，能夠将公式：=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3))更改為：=SUMPRODUCT((D3:D24=J3),(F3:F24=K3))？

2、多條件求和時，為什麼=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3)*G3:G24)和=SUMPRODUCT((D3:D24=J3)*(F3:F24=K3),G3:G24)均可使用？

7、排序。

目的：對相應“學曆”下的“月薪”進行排序。

方法：

在目标單元格中輸入公式：=SUMPRODUCT((F$3:F$24=F3)*(G$3:G$24>G3)) 1。

思考

1、公式有幾個參數？

2、為什麼 1，而不是直接寫成：=SUMPRODUCT((F$3:F$24=F3)*(G$3:G$24>G3))？歡迎在留言區留言讨論哦！

結束語：

說了很多，都是關于Sumproduct的實際應用，可以計數、求和，還不止，計數中還可以實現單條件計數、多條件計數；求和中可以實現單條件求和、多條件求和；除此之外，還可以排序，當然了，排序不僅僅是對數值的排序（怎麼排？？？），還可以附加多個條件進行排序，覆蓋了Sum、Sumif、Sumifs、Countif、Countifs、Rank等函數的功能，難道還不夠強大？

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!