Excel 怎么用 SUMPRODUCT 做多条件统计
条件一多,筛选加手数就成了体力活。SUMPRODUCT 把多个条件写进一个公式,省掉辅助列和数组三键,代价是区域要等长、整列引用会拖慢重算。
条件一多,筛选加手数就成了体力活。SUMPRODUCT 的好处是不用辅助列、不用数组三键,把几个条件直接写进一个公式里,改条件只改一个参数。代价是它比 SUMIFS 吃性能,也更容易因为区域长度不一致报错。
入口在哪
它不是一个按钮,是公式。在任意空白单元格里输入等号,键入 SUMPRODUCT,Excel 和 WPS 都会给出参数提示。写法是先把每个条件写成一段括号,最后再乘上要求和的那一列。
如果输入函数名后没有提示,通常是输入法还在中文状态,把括号或逗号打成了全角。切回英文半角就能被识别。
具体步骤
- 先把条件区域选成具体区间,比如 A2:A1000,不要选整列 A:A。整列会让公式把上百万个空单元格也算一遍,表一大就卡。
- 多条件计数这样写:SUMPRODUCT((A2:A1000=「华东」)*(B2:B1000>100))。两个括号相乘,同时成立才记 1,最后把 1 加起来就是条数。
- 要多条件求和,在后面再接一段乘上金额列:SUMPRODUCT((A2:A1000=「华东」)*(B2:B1000>100)*C2:C1000)。
- 加第三个条件就再乘一个括号。条件之间一律用星号连接,不要换成逗号,两者含义不同,混用会算错。
- 条件里要写文字时,把条件先写在旁边的单元格里,公式直接引用这个单元格。这样既省掉手写引号的麻烦,条件也随时能改。
- 写完回车即可,普通公式就能出结果,不需要按 Ctrl+Shift+Enter 组合键。
几个坑
- 区域长度必须一致。A2:A1000 配 C2:C999 会直接报值错误,这是最常见的错。
- 文本型数字不参与比较。从系统导出的数字常带前置空格或者存成文本,大于 100 这类条件会把它们一律判为假。先用分列或 VALUE 函数转成数值。
- 整列引用拖慢重算。几千行看不出差别,十万行以上每次改单元格都要重算一遍全表。
- 乘号不是逻辑与。条件多时有人用逗号分隔,等于先让各数组对应位置相乘再求和,条件越多结果越离谱。
- 空单元格参与乘法会被当成 0,不会报错,但求和列有空值时结果会少算,记得核对总数。
怎么验证
拿一张小表手动对账:先用筛选把条件筛出来,看状态栏的计数和求和,再和公式结果比。两个数一致,说明条件写对了。
再故意改一个条件值,看结果是否跟着变。如果改了条件结果不动,多半是条件被写死成了具体值,或者公式引用的是一份复制出来的旧区域。
最后把公式复制到相邻列,检查相对引用有没有跑偏。该锁的区域先锁住,再往下拖。
常见问题
SUMPRODUCT 和 SUMIFS 该用哪个
条件都是等于、大于这类简单比较,优先 SUMIFS,算得更快。需要按行内多个字段组合判断,或者条件本身是计算结果时,SUMPRODUCT 更灵活。
为什么结果是 0 而不是报错
说明公式本身没写错,只是没有一行同时满足所有条件。逐条把条件单独拿出来算一遍,通常能定位到是哪个条件写反了,或者文字前后多了空格。
能不能统计不重复的个数
可以,思路是把一列除以它自己出现的次数,再把结果加起来。表大时这个写法很慢,建议改用数据透视表。
一句话总结:SUMPRODUCT 把多个条件写成括号相乘,好处是一个公式搞定,代价是区域要等长、别引整列、文本数字先转成数值。