【原】不管Excel是否隐藏行，这个公式都能搞定求和，真神奇！

与 30万 读者一起学Excel

现在要对带颜色的内容进行条件求和，又该如何做呢？

按照昨天的思路，这里可以用一个辅助列，填上1，然后用SUMIFS函数进行条件求和。

=SUMIFS($C$2:$C$11,$A$2:$A$11,A15,$D$2:$D$11,1)

现在将问题进一步拓展，假设筛选的内容没有规律，甚至包含隐藏行，如何进行条件求和？

也就是说，不管有没筛选（或者隐藏），都可以对可见单元格的内容进行条件求和。

这里，卢子分享一条神奇的公式，可以满足以上所有需求。

=SUMPRODUCT((A15=$A$2:$A$11)*SUBTOTAL(109,OFFSET($C$1,ROW($1:$10),0)))

1.正常情况下求和

用SUMIF验证，结果正确。

=SUMIF($A$2:$A$11,A15,$C$2:$C$11)

2.筛选情况下求和

用SUMIFS+辅助列验证，结果照样正确。

=SUMIFS($C$2:$C$11,$A$2:$A$11,A15,$D$2:$D$11,1)

3.隐藏行的情况下求和

同样用SUMIFS+辅助列验证，结果照样正确。

=SUMIFS($C$2:$C$11,$A$2:$A$11,A15,$D$2:$D$11,1)

好，验证全部通过，下面来说明公式的含义。

SUBTOTAL函数第一参数有很多种用法，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键得出结果。

这里可能有人觉得用OFFSET($C$1,ROW($1:$10),0)很麻烦，想直接引用区域，不过行不通！SUBTOTAL(9,$C$2:$C$12)这种是错误的，只能得到求和的值。

好，最后结合SUMPRODUCT条件求和的语法即可解决。

=SUMPRODUCT((条件=条件区域)*求和区域)

=SUMPRODUCT((A15=$A$2:$A$11)*SUBTOTAL(109,OFFSET($C$1,ROW($1:$10),0)))

平常多交流，这样自己也能记得牢固，同时学到更多知识，一举两得。

源文件：

推荐：Excel忽略隐藏行或者列求和，你会吗？

上篇：Excel按颜色求和，公式原来是这样设置出来的！

作者：卢子，清华畅销书作者，《Excel效率手册 早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban）