与 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不加班(ID:Excelbujiaban)
联系客服