ARTICLE · 1386806
Excel小技巧:多条件统计三件套,为什么你算出来是 0

SUMIFS / COUNTIFS / SUMPRODUCT “多条件统计三件套”讲一讲。多条件统计的错误率远高于其他公式,因为它有四道独立的坎——参数顺序、条件写法、数据类型、区域尺寸。错任何一道,结果都是"看着能用其实不对"。一、参数顺序:SUMIF 和 SUMIFS 是反的
很多人从 SUMIF 升级到 SUMIFS,直接照抄原来的写法:
=SUMIFS(区域A,">1000",求和列) ← 错,Excel 把"求和列"当成了条件=SUMIFS(求和列,区域A,">1000") ← 对记法:单条件用 SUMIF 时求和区域在最后;多条件用 SUMIFS 时求和区域第一个。 一句话——多条件的,先说要算什么。
二、条件的六种写法(含错误示范)
条件这一栏看着简单,规则其实很硬。
"华东" | 华东 | |
">10000" | >10000 | |
">="&D1 | ">=D1" | |
"<>"&"已完成" | <>已完成 | |
"*科技*" | "*科技*"(* 被当乘号,见第五节) | |
"""<>" | " " 不等于真空白 |
最容易错的是第三行:把引用和运算符拼接。
">="&D1 正确,D1 的值会被拼进条件"&D1&">=100" 错误,引号位置乱了">=&D1" 错误,& 被吞进字符串里& 是连接符,条件里的引号必须在 & 之前闭合,这一点靠多敲两次就记住了。
还有一个隐藏规则:通配符 * ? 在 SUMIFS / COUNTIFS 里默认可用,不需要勾任何东西。所以:
=SUMIFS(金额,客户,"*有限公司")能正常跑。但要小心:如果客户名里真的有 * 或 ?,得用 ~ 转义,否则误匹配。
三、日期条件:你的月度统计为什么会少
这是"结果偏小"的头号原因,两个坑叠在一起。
坑一:直接写文本日期。
=SUMIFS(金额,日期列,">=2026-10-01") ← 依赖系统区域设置,不稳=SUMIFS(金额,日期列,">="&DATE(2026,10,1)) ← 稳日期在 Excel 内部其实是序列值(第005期讲过这个底层原理),用 DATE() 拼出来的条件永远是标准比较,不受你电脑是"2026/10/1"还是"2026-10-01"显示格式影响。
坑二:用 <= 月末,会漏掉当月最后一天的数据。
如果日期列里带时分秒(从业务系统导出的单据时间戳经常带),那么 <=2026-10-31 实际是 <=2026-10-31 00:00:00,10月31日当天有时刻的数据一条都不算。
正确写法是"大于等于月初,且小于次月初":
=SUMIFS(金额,日期,">="&DATE(2026,10,1),日期,"<"&DATE(2026,11,1))月初用 >=,月末的下一天用 <,两头都闭得住,带不带时分秒都不漏。
如果结果直接是 0,先怀疑日期列根本不是日期。 文本型的 2026-10-05 不参与大小比较。判断方法一行:
=COUNT(日期列)返回 0 或远小于行数 → 这一列是文本,回去按第005期用分列转成真日期,别在公式上继续绕。
四、文本型数字:条件统计的双向坑
第020期讲"看着一样其实不一样"时埋过伏笔,这里讲透。
SUMIFS / COUNTIFS 会做类型转换:条件写数字 1234,格子里存文本 "1234" 也算命中;反过来条件写文本,数字也会被算进去。
这会导致两类错误:
第一类,数多了。 你想统计"有多少条真数字金额",结果文本型的脏数据一起被算进来,合计看着对,其实是两类数据混着加的。
第二类,前导零编码误匹配。 这个更阴。条件是 "01234" 时,Excel 会把它当数字 1234 比较,于是格子里的 1234、01234、001234 全部命中。做物料编码、工号、科目编码的条件统计时,结果会莫名其妙偏大。
要按字面严格匹配,交给 SUMPRODUCT + EXACT:
=SUMPRODUCT(--EXACT(编码列,"01234"),数量列)EXACT 不做类型转换、区分大小写,逐格比对才可信。
根治办法还是那句话: 标识类字段在录入和导入时就按文本存(第026期分列第3步设文本、第025期先设文本格式再输长数字)。源头类型对了,公式层就不用天天打补丁。
五、SUMPRODUCT:乘是"且",加是"或"
SUMIFS 只能表达"所有条件同时满足"(AND)。要写"或",或者要做更复杂的判断,就得用 SUMPRODUCT。
规则就两条:
条件A 且 条件B → 条件A * 条件B条件A 或 条件B → 条件A + 条件B因为 TRUE 当 1、FALSE 当 0,相乘是"全 1 才 1",相加是"有 1 就非 0"。
AND 版(华东且金额>1万):
=SUMPRODUCT((区域列="华东")*(金额列>10000),金额列)OR 版(北京或上海):
=SUMPRODUCT(--((城市列="北京")+(城市列="上海")>0),金额列)外面那层 (...>0) 和 -- 都不能省。相加得到的是 0/1/2 这样的数值(一行同时命中两个分支时会变成 2,金额就被算两遍),>0 把它压回"命中与否",-- 再把 TRUE/FALSE 洗成 1/0。
把 TRUE/FALSE 变成 1/0,用双重负号 --,就是第020期数差异行数时出现过的那一位:-TRUE 变 -1,再负一次变 1。
=SUMPRODUCT(--(状态列="已完成"),金额列)SUMPRODUCT 有两条硬约束,记住能省很多调试:
第一,这里不能用通配符。 因为 * 在这个函数里是乘号。想表达"包含某词",换成 ISNUMBER(SEARCH("关键词",列)):
=SUMPRODUCT(--ISNUMBER(SEARCH("逾期",备注列)),金额列)第二,范围必须写精确,别用整列。SUMPRODUCT 会把范围内的每一格都算一遍,你写 A:A 就是让它算 1048576 行 × 好几个数组,几万行的表直接卡死。
对照着记:SUMIFS 用整列引用通常还能扛(它只按条件筛,代价相对小),SUMPRODUCT 必须给精确范围,比如 $A$2:$A$5000。表上百万行时,这个差别就是"卡住"和"能干活"的区别。
六、结果为 0 或报错,按这个顺序查
别乱改公式,按顺序走一遍,六步之内必定位:
- 报错 #VALUE!
→ 条件区域和求和区域尺寸不一致。SUMIFS 要求所有区域行数列数完全相同 - 结果 0,条件是日期
→ 先 =COUNT(日期列),是文本就回去转真日期 - 结果 0,条件是数字
→ 数据被存成文本,或你条件写的是文本,两边格式对不上 - 结果 0,条件含空格
→ 数据里有前后空格,用第020期的 TRIM 清一遍 - 结果偏小
→ 月末用了 <=,改成次月初< - 结果偏大
→ 条件被当成数值比较,把前导零编码、文本型数字混算了,改用 SUMPRODUCT + EXACT
这套顺序比我一开始说的"四道坎"更贴近实际操作:先确认尺寸,再确认类型,最后确认边界。
今天的总结
多条件统计,四条铁律:
一,SUMIFS 求和区域放最前面,所有区域尺寸必须一模一样。
二,条件里的引用一定要 & 拼接,日期用 DATE() 别用文本,月份统计统一写 >=月初 加 <次月初。
三,COUNTIFS/SUMIFS 会把文本数字和真数字混算,前导零编码统计请交给 SUMPRODUCT(--EXACT(...))。
四,AND 用乘、OR 用加,-- 把 TRUE/FALSE 洗成 1/0;SUMPRODUCT 不吃通配符、也不能用整列。
算出 0 别慌,按六步走。多数时候问题不在你逻辑不行,而是数据类型在偷偷骗你。
——E晓分享,共同进步——















































































