专栏名称: 秋叶Excel

和秋叶一起学Excel，免费获取Excel模板大全、插件合集！还有Excel真人视频、图文教程，助你轻松掌握Excel函数、图表、透视表……一起成为办公效率达人！

当数字不能用Sum函数求和时，你必须学会这3种方法！

秋叶Excel · 公众号 · · 2024-05-20 11:04

正文

本文作者：小花

本文编辑：竺兰

Excel 中有很多可用于条件求和的函数，如 SUMIF、SUMIFS、SUMPRODUCT。

但在一些特殊的数据表中进行条件求和，我们或许还需要其他函数的帮助。

今天，小花就分享三种特殊情况下的求和公式。

筛选后求和

审计员小 K 发现某下属公司部分报销费用可能存在风险，于是逐笔初筛出来。

现需设置一个公式， 按部门统计发生额 。随后，小 K 将进行二次筛选，当风险项被排除时，也能够自动剔除该金额。

这一问题的难点在于，如何确定每一条记录是否被筛选出来？虽然肉眼很容易分清，但如何让计算机明白却不那么容易。

好在，Excel 中有一个专门的筛选统计函数—— SUBTOTAL 函数。

借助 SUBTOTAL 剔除隐藏单元格后进行统计的功能，我们可以设置如下公式完成筛选情况下的条件求和。

筛选下的条件求和公式：

=SUMPRODUCT(($C$8:$C$41)*SUBTOTAL(3,OFFSET($C$7,ROW($C$8:$C$41)-7,0))*($A$8:$A$41=A2))

公式说明 👇

❶ OFFSET($C$7,ROW($C$8:$C$41)-7)

ROW($C$8:$C$41)-7 函数返回一组 1-34 的有序数组，OFFSET 函数根据该数组，逐一偏移返回 C8:C41 的每一个单元格。这与直接引用 C8:C41 是不同的，前者将每个单元格都视为一个单独的区域，而后者则将 C8:C41 视为一个整体。

❷ SUBTOTAL(3,①)

3 是计数功能代码，此处 SUBTOTAL 识别 C8:C41 的每个单一单元格区域是否隐藏，如果该单元格隐藏，则统计结果为 0，否则为 1。即，②的结果为 1 则表示该单元格在筛选结果中。

❸ SUMPRODUCT(($C$8:$C$41)*②*($A$8:$A$41=A2))

SUMPRODUCT 函数的经典用法，C8:C41 是求和区域，②和($A$8:$A$41=A2)是条件区域，求和区域和条件区域一一对应相乘再求和，即为条件求和结果。

合并单元格求和

合并单元格对报表的视觉呈有其「独到」之处，但其痛点也很「毒辣」——无法直接进行条件统计。

比如，在存在合并单元格的情况下，直接进行条件求和，计算结果多半都是错误的。

这是因为，合并单元格过程中，除首个单元格外，其余单元格的内容都会被清除。

要想能够在这种情况下实现条件求和，我们需要用 LOOKUP 函数来「复原」空白值。

合并单元格下的条件求和公式：

{=SUM(($C$8:$C$20)*(LOOKUP(ROW($A$8:$A$20),IF($A$8:$A$20<>"",ROW($A$8:$A$20),""),$A$8:$A$20)=A2))}

公式说明 👇

❶ IF($A$8:$A$20<>"",ROW($A$8:$A$20),"")

对 A8:A20 进行判断，如不为空白，返回行号，否则返回空白。也就是说，每一个合并单元格首行都返回其行号。

❷ LOOKUP(ROW($A$8:$A$20),①,$A$8:$A$20)

如果 A8:A20 中的某个单元格为所在合并单元格的首行，则其行号会在①中出现，LOOKUP 返回其本身；

如果 A8:A20 中的某个单元格不是所在合并单元格的首行，则它在①中与空白对应，查询其行号时，LOOKUP 匹配到小于且接近去其行号的最大值，即其所在合并单元格首行的行号，最终返回所在合并单元格首个单元格的值。由此，所有因合并而被清除的单元格值都得到复原。

❸ {=SUM(($C$8:$C$20)*(②=A2))}

利用 SUM 的数组运算，返回乘积和，和 SUMPRODUCT 条件求和原理类似，此处必须使用 SUM 函数，按【Ctrl+Shift+Enter】执行数组运算。

去除重复值求和

条件求和的另外一种特殊情况是，求和区域存在部分重复值，需要去重后再求和才能得到准确结果。

比如下图中，部分人员有重复的，无法直接进行条件求和。

这时候，我们需要使用 COUNTIF 函数来「稀释」重复值，再求和。

去重后的条件求和公式：

=SUMPRODUCT(($A$7:$A$18=$A2)*($C$7:$C$18)/COUNTIF($B$7:$B$18,$B$7:$B$18))

公式说明 👇

COUNTIF($B$7:$B$18,$B$7:$B$18)统计每个姓名出现的次数 n，将其每一行工资对应「稀释」为其本身的 1/n，再进行条件求和，每个人的 1/n 工资都被计算了 n 次，这就实现了去重后的条件求和。

以上，就是小花分享的三种特殊情况下的条件去和公式，包括：

❶ 利用 SUBTOTAL 识别筛选再完成条件求和；

❷ 利用 LOOKUP 复原合并单元格缺失值完成包含合并单元格的条件求和；

❸ 利用 COUNTIF 稀释原值完成去重后的条件求和。

你学会了吗？

随着 Excel 版本不断更新，也涌现了 SCAN、LAMBDA、UNIQUE、FILTER 等函数，能够简化上述公式或者公式中的某个片段，有条件的小伙伴不妨一试。

学无止尽，与君共勉！

当数字不能用Sum函数求和时，你必须学会这3种方法！

正文

请到「今天看啥」查看全文