SUMIF函數通過跟VLOOKUP函數過招後,優勢盡顯。可惜,強中自有強中手,遇上求和之王SUMPRODUCT函數,SUMIF函數也只能甘拜下風。
1.統計每個培訓班總的報名人數。
試著用SUMIF函數統計,不過結果是錯誤的,只是統計了第1期的報名人數而已。
=SUMIF($A$2:$A$5,G2,$B$2:$E$5)
而用SUMPRODUCT函數卻可以輕易解決這個問題。
=SUMPRODUCT(($A$2:$A$5=G2)*$B$2:$E$5)
SUMPRODUCT函數跟SUM函數的數組公式條件求和語法一樣。
=SUMPRODUCT((條件區域=條件)*求和區域)
2.統計12個月的總銷售毛利。
SUMIF函數無能為力。
雖然有姓名處有合併單元格,不過不影響SUMPRODUCT函數統計。
=SUMPRODUCT((B2:B16="銷售毛利")*C2:N16)
3.統計每個月的金額。
這裡嘗試了用SUMIF函數,一回車,就彈出此公式有問題的警告對話框。SUMIF函數不管是求和區域或者條件區域,都不允許用其他函數。
=SUMIF(MONTH($A$2:$A$60),D2,$B$2:$B$60)
而用SUMPRODUCT函數依然可以輕易解決這個問題。
=SUMPRODUCT((MONTH($A$2:$A$60)=D2)*$B$2:$B$60)
4.在篩選狀態下,統計每個培訓班的人數。
默認情況下,不管是SUMIF函數還是SUMPRODUCT函數,都是無法直接實現的。
而SUMPRODUCT函數因為可以結合其他函數,雖然難度很大,但依然可以解決問題。
=SUMPRODUCT(($A$2:$A$10=A14)*SUBTOTAL(103,OFFSET($A$1,ROW($1:$9),0))*$C$2:$C$10)
OFFSET函數就是依次獲取每一行的數據,SUBTOTAL函數就是判斷每一行是不是隱藏了,如果沒有隱藏就顯示1,否則顯示0。
公式的魅力,在於函數之間的嵌套組合,從而產生無窮變化,將不可能變成可能。
閱讀更多 excel咖喱魚丸 的文章