分享SUMPRODUCT

angleksun

1 SUMPRODUCT基础
1.1 概述
SUMPRODUCT通常用于2个或多个数组中对应的每个元素相乘并求和。例如，下图所示的表

使用=SUMPRODUCT(A1:A3,B1:B3)，结果为32。具体的计算为：1×4+2×5+3×6。
可以扩充到多个数组。注意，虽然这个函数是相乘和求和，但是没有乘号或加号。然而，要有逗号，用来分开要相乘的数组。
1.2 SUMPRODUCT 函数作为COUNTIF函数使用
假设在A2:A20中有一些数据，下面通过一个简单的条件测试开始：

=IF(A2=”test”,1,0)

在单元格B2中输入上述公式，向下复制到B20，然后简单地合计B2:B20，这将统计在A2:A20中有多少项等于“test”。当然，能够使用一个简单的公式COUNTIF(A2:A20,”test”)。这里，不详细讨论这个公式，重点关注下面使用的更聪明的方法。
如果在单元格B2中输入=A2:A20=”test”，会得到TRUE或FALSE，这取决于单元格A2中的值是否为“test”。
现在，使用SUMPRODUCT函数，如下

=SUMPRODUCT(A2:A20=”test”)

得到0，即使在单元格区域A2:A20里有一些值为“test”。难道SUMPRODUCT函数没有帮助吗？我们必须再做一些工作。
使用公式=SUMPRODUCT(A2:A20=”test”)，我们试图汇总一组TRUE和FALSE的值。因此，我们需要强迫这组TRUE和FALSE值成为1和0，以便汇总为有意义的结果。为什么是1和0，而不是23或76呢？由于我们简单地试图统计A2:A20中值等于”test”的数量，因此结果为TRUE就在总计中加1，有效地统计相匹配的单元格数量。
有一种简单的方式来实现。Excel将TRUE当作1，FALSE当作0。如果我们在这些值中执行某种算术运算，那么可以强迫使一组TRUE/FALSE转换为1/0。一种方式是将结果数组乘1，如下：

=SUMPRODUCT((A2:A20=”test”)*1)

有效地将每次结果乘1，得到1或0，并在结果数组中存储这些值。
另一种方式是加0：

=SUMPRODUCT((A2:A20=”test”)+0)

对每次结果加0，同样得到1或0，并在结果数组中存储这些值。
有许多其它方式，其中一种是在前面添加两个减号：

=SUMPRODUCT(–(A2:A20=”test”))

第一个减号否定每次的结果，得到值为-1和0的数组；第二个减号再次否定，得到值为1和0的数组。即第一个减号强迫使TRUE和FALSE为数字，第二个切换-1为1，因此不会弄乱结果。
1.3 小结
本文中我们学习了两点。第一，可以使用SUMPRODUCT包括条件测试，返回能够进一步处理的数组值。第二，可以通过使用算术运算强迫条件成为能够合计的数值，例如双减号、乘1或加0。这是所有SUMPRODUCT解决方案的基本原理。
至此，我们仅仅介绍了如何统计数组里与所给条件匹配的项目，这只是强大的SUMPRODUCT函数的用途之一。

whuboyxj

投行实用函数介绍，可供新手学习！

whuboyxj

补充一下：sumproduct最大的作用是作为筛选求和函数处理，sumproduct(条件数列，数据数列)，可以求得满足条件的数据之和，这样可以减少先筛选再求和的操作。