【原】忽略隱藏?cái)?shù)據(jù)按條件求和，操作技巧難度較高。沒有SUBTOTAL函數(shù)，公式根本做不到！

歡迎轉(zhuǎn)發(fā)和點(diǎn)一下“在看”，文末留言互動!

置頂公眾號或設(shè)為星標(biāo)及時(shí)接收更新不迷路

小伙伴們好，大家都知道在EXCEL中有一個(gè)函數(shù)可以忽略隱藏行而進(jìn)行統(tǒng)計(jì)、求和等任務(wù)，那就是SUBTOTAL函數(shù)。

那么，在使用SUMPRODUCT函數(shù)時(shí)，是否也能夠像SUBTOTAL函數(shù)一樣，可以忽略隱藏行呢？例如下面這道題目，在區(qū)域有篩選的情況下，如何計(jì)算微波爐的產(chǎn)量總和？

這里單獨(dú)使用SUMPRODUCT函數(shù)是沒有辦法解決這個(gè)問題的。我們還是必須要配合SUBTOTAL函數(shù)。

在單元格E2中輸入下列公式，回車確認(rèn)即可。

=SUMPRODUCT(($B$2:$B$17="微波爐")*($C$2:$C$17)*SUBTOTAL(3,OFFSET($A$1,ROW(A2:$A$17)-1,)))

下面對這個(gè)公式做個(gè)簡單的分析。

($B$2:$B$17="微波爐")*($C$2:$C$17)

上面這部分，是求和區(qū)域和條件。

SUBTOTAL(3,OFFSET($A$1,ROW(A2:$A$17)-1,)

利用SUBTOTAL函數(shù)對C列求和區(qū)域的篩選狀態(tài)做出判斷。當(dāng)單元格正常顯示時(shí)，SUBTOTAL函數(shù)返回?cái)?shù)字“1”；當(dāng)單元格隱藏時(shí)，SUBTOTAL函數(shù)就返回?cái)?shù)字“0”。

上面SUBTOTAL函數(shù)返回的結(jié)果是{0;1;1;1;1;0;0;0;0;1;1;1;1;1;0;0;0}

SUMPRODUCT(($B$2:$B$17="微波爐")*($C$2:$C$17)*SUBTOTAL(3,OFFSET($A$1,ROW(A2:$A$17)-1,)))

($B$2:$B$17="微波爐")*($C$2:$C$17)*SUBTOTAL()，三段公式相乘，結(jié)果中屏蔽了所有非“微波爐”的數(shù)據(jù)。最后由SUMPRODUCT函數(shù)求和。

請注意，這個(gè)公式中也可以將SUMPRODUCT函數(shù)換成SUM函數(shù)，只不過就要三鍵回車了。

我就知道你“在看”