與 30萬 讀者一起學(xué)Excel
現(xiàn)在要對帶顏色的內(nèi)容進(jìn)行條件求和，又該如何做呢？
按照昨天的思路，這里可以用一個輔助列，填上1，然后用SUMIFS函數(shù)進(jìn)行條件求和。
=SUMIFS($C$2:$C$11,$A$2:$A$11,A15,$D$2:$D$11,1)
現(xiàn)在將問題進(jìn)一步拓展，假設(shè)篩選的內(nèi)容沒有規(guī)律，甚至包含隱藏行，如何進(jìn)行條件求和？
也就是說，不管有沒篩選（或者隱藏），都可以對可見單元格的內(nèi)容進(jìn)行條件求和。
這里，盧子分享一條神奇的公式，可以滿足以上所有需求。
=SUMPRODUCT((A15=$A$2:$A$11)*SUBTOTAL(109,OFFSET($C$1,ROW($1:$10),0)))
1.正常情況下求和
用SUMIF驗證，結(jié)果正確。
=SUMIF($A$2:$A$11,A15,$C$2:$C$11)
2.篩選情況下求和
用SUMIFS+輔助列驗證，結(jié)果照樣正確。
=SUMIFS($C$2:$C$11,$A$2:$A$11,A15,$D$2:$D$11,1)
3.隱藏行的情況下求和
同樣用SUMIFS+輔助列驗證，結(jié)果照樣正確。
=SUMIFS($C$2:$C$11,$A$2:$A$11,A15,$D$2:$D$11,1)
好，驗證全部通過，下面來說明公式的含義。
SUBTOTAL函數(shù)第一參數(shù)有很多種用法，109代表忽略隱藏值求和。
OFFSET($C$1,ROW($1:$10),0)就是C1單元格分別向下1行、2行……10行，也就是依次得到C2、C3……C11。
SUBTOTAL(109,OFFSET($C$1,ROW($1:$10),0))綜合起來，就是顯示可見單元格的值，隱藏起來的值就顯示0，可以在編輯欄按F9鍵得出結(jié)果。
這里可能有人覺得用OFFSET($C$1,ROW($1:$10),0)很麻煩，想直接引用區(qū)域，不過行不通！SUBTOTAL(9,$C$2:$C$12)這種是錯誤的，只能得到求和的值。
好，最后結(jié)合SUMPRODUCT條件求和的語法即可解決。
=SUMPRODUCT((條件=條件區(qū)域)*求和區(qū)域)
=SUMPRODUCT((A15=$A$2:$A$11)*SUBTOTAL(109,OFFSET($C$1,ROW($1:$10),0)))
平常多交流，這樣自己也能記得牢固，同時學(xué)到更多知識，一舉兩得。
源文件：
上篇：Excel按顏色求和，公式原來是這樣設(shè)置出來的！
作者：盧子，清華暢銷書作者，《Excel效率手冊 早做完，不加班》系列叢書創(chuàng)始人，個人公眾號：Excel不加班（ID：Excelbujiaban）
