Excel SUMPRODUCT 函数：相乘求和与按条件计数

=SUMPRODUCT(B2:B6,C2:C6) 把 B 列中的每个数量乘以旁边 C 列中的单价，再把结果加起来。它在一个单元格中就给出订单总额，不需要一列小计。

公式重置

F2fx

=SUMPRODUCT(B2:B6,C2:C6)

	A	B	C	D	E	F
1	Item	Qty	Price	Line total		Total
2	Pen	4	$1.50	$6.00		$30.70
3	Notebook	2	$3.25	$6.50		$30.70
4	Folder	5	$0.80	$4.00
5	Stapler	1	$7.90	$7.90
6	Marker	3	$2.10	$6.30

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

F2 和 F3 都显示 $30.70。D 列中的小计只是为了说明 SUMPRODUCT 做了什么：4 × 1.50、2 × 3.25，依此类推，最后求和。修改某个数量，两个合计都会跟着变。

SUMPRODUCT 的语法

=SUMPRODUCT(array1, [array2], [array3], ...)

• 每个数组是一个区域，或者能得到区域的计算，所有数组的大小都必须相同，否则 SUMPRODUCT 返回 #VALUE!。
• 有两个或更多数组时，相同位置上的值相乘，然后把乘积加起来。
• 只有一个数组时，它只是把这个数组加起来，下面的条件写法正是利用了这一点：=SUMPRODUCT((A2:A7="North")*C2:C7) 只有一个已经相乘过的数组。
• 作为单独参数传入的文本算作 0。* 计算中的文本会导致 #VALUE!。

在所有 Excel 版本中，SUMPRODUCT 都能直接处理数组，不需要按 Ctrl+Shift+Enter（Mac 上按 Cmd+Shift+Enter），所以在 SUMIFS 出现之前，它是条件求和的标准工具，在 SUMIFS 处理不了的情况下至今仍是。

带条件的 SUMPRODUCT

对区域做比较，比如 A2:A7="North"，每一行返回一个 TRUE 或 FALSE。乘以它，就会保留结果为 TRUE 的行（×1），并把其他行变成 0（×0）。把两个比较相乘就是“且”。

公式重置

F2fx

=SUMPRODUCT((A2:A7="North")*C2:C7)

	A	B	C	D	E	F
1	Region	Product	Sales		Formula	Result
2	North	Apple	120		North sales	230
3	South	Pear	45		North Apple sales	150
4	North	Pear	80		Count North	3
5	East	Apple	55		Count over 50	4
6	South	Apple	200		Without --	0
7	North	Apple	30

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

F2 把 North 的三行加起来：230。F3 把两个条件相乘，所以只有两个条件都成立的行才会被计入：150。要计数而不是求和，就去掉数值，再用 --（两个负号）把 TRUE/FALSE 变成数字：F4 统计出 3 行 North。F6 说明了 -- 为什么重要：SUMPRODUCT 不会把 TRUE 值加起来，所以不加它的公式返回 0。

前四个公式的结果和 SUMIF、SUMIFS、COUNTIF 相同。下一节才是 SUMPRODUCT 真正发挥作用的地方。

SUMIFS 无法表达的条件

SUMIFS 把一列和一个固定的条件比较。它不能取日期的月份，不能让两列互相比较，也不能在相加之前先把数量乘以单价。SUMPRODUCT 可以，因为每个条件都只是普通的计算。

公式重置

G2fx

=SUMPRODUCT((MONTH(B2:B7)=2)*D2:D7)

	A	B	C	D	E	F	G
1	Region	Date	Target	Actual		Formula	Result
2	North	2026-01-05	100	120		February sales	135
3	South	2026-01-12	60	45		Rows over target	3
4	North	2026-02-03	90	80		North or East sales	285
5	East	2026-02-18	50	55		Above target by	75
6	South	2026-03-02	150	200
7	North	2026-03-20	40	30

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

• G2 取每个日期的 MONTH，保留 2 月的行：80 + 55 = 135。它会把任何年份的 2 月都加进来；只要某一年时，再乘上 *(YEAR(B2:B7)=2026)。
• G3 逐行比较两列，统计 Actual 超过 Target 的行数。
• G4 是“或”：把两个条件相加，任一条件成立时得到 1（两个都成立时得到 2，所以要加上 >0）。North 或 East：285。
• G5 把超出目标的各行超出了多少加起来，只计算超出目标的行。

用 SUMPRODUCT 计算加权合计和加权平均值

数量乘以单价就是加权合计，还可以在上面加条件。同样的思路再除以权重之和，就得到 加权平均值：=SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6) 是每件售出商品的平均单价。

公式重置

G2fx

=SUMPRODUCT((A2:A7="North")*C2:C7*D2:D7)

	A	B	C	D	E	F	G
1	Region	Product	Qty	Price		Formula	Result
2	North	Apple	10	$1.20		North revenue	$31.00
3	South	Pear	4	$1.50		All revenue	$61.00
4	North	Pear	6	$1.50		Average price per item	$1.36
5	East	Apple	8	$1.20
6	South	Apple	12	$1.20
7	North	Plum	5	$2.00

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

North 以 $1.20 卖出 10 个苹果，以$1.50 卖出 6 个梨，以 $2.00 卖出 5 个李子，所以 G2 显示$31.00。直接对单价求平均，会把李子当作和苹果卖得一样多；G4 则用数量给每个单价加权。

练习：带条件的收入

公式重置

G2fx

	A	B	C	D	E	F	G
1	Region	Product	Qty	Price		Formula	Result
2	North	Apple	10	$1.20		South revenue
3	South	Pear	4	$1.50
4	North	Pear	6	$1.50
5	East	Apple	8	$1.20
6	South	Apple	12	$1.20
7	North	Plum	5	$2.00

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

轮到你了： 计算 South 的收入：数量乘以单价，只计算 South 的行。在 G2 中写公式。

检查显示答案

SUMPRODUCT 和 SUMIFS 的对比，以及两种常见错误

只要 SUMIFS 能完成，就优先用它。它更易读，在几万行数据上更快，而且接受整列引用。=SUMPRODUCT((A:A="North")*C:C) 要对一百多万行做乘法，一碰到 C1 中的标题文本就会返回 #VALUE!，所以要给 SUMPRODUCT 精确的区域，比如 A2:A500。

你会遇到的两种错误：

• 区域大小不同导致的 #VALUE!。 =SUMPRODUCT(B2:B6,C2:C7) 会出错。每个区域都必须覆盖相同的行。
• 相乘的区域中有文本导致的 #VALUE!。 C2:C7 中的标题或“n/a”会让 (A2:A7="North")*C2:C7 出错，因为文本不能相乘。让区域从标题下方开始，或者把数值作为单独的参数传入：=SUMPRODUCT(--(A2:A7="North"),C2:C7) 会把 C 列中的文本当作 0。