SUMPRODUCT函数详解

下面将介绍SUMPRODUCT函数的实际应用示例。

示例1：统计内容为日期的单元格区域A42:A407中早于今天的数量，单元格区域J42:J407是一组变量数组值。

解决方案：使用($A$42:$A$407<TODAY())处理日期测试。在单元格区域中已经设置了变量数组值，需要联合使用MATCH和ISNUMBER函数。

=SUMPRODUCT((ISNUMBER(MATCH(J42:J407,Fred,0)))*($A$42:$A$407<TODAY()))

示例2：统计某给定日期后的数量。

解决方案：如果测试日期在某个单元格中，那么公式为：

=SUMPRODUCT((C5:C309>$A$1)*(H5:H309=”A”))

下面的公式展示了一项在所有版本Excel中，在SUMPRODUCT函数中测试日期的技术，在公式中嵌入日期字符串：

=SUMPRODUCT((C5:C309>(–(“2004/05/31”)))*(H5:H309=”A”))

示例3：在公式中不输入多个条件判断，而是将它们输入到单元格中，只是在公式中引用这些单元格，可以吗？

解决方案：这似乎是一个简单的问题，使用下面的公式：

=SUMPRODUCT((B5:B63=L1:N1)*(C5:C63))

然而，公式运行失败，因为在列中而不是在行中输入的是条件判断，因此需要使用TRANSPOSE函数来满足SUMPRODUCT函数的要求：

=SUMPRODUCT((B5:B63=TRANSPOSE(P46:P48))*(C5:C63))

由于使用了TRANSPOSE函数，所以必须作为数组公式输入上面的公式。

示例4：初始公式是：

=SUMIF(J2:J196,J209,L2:L196)

但还需要汇总下列单元格区域：R2:R196，U2:U196，V2:V196，Z2:Z196

解决方案：通过为每个单独的测试区域使用单独的SUMPRODUCT函数，很容易解决。但下面的方法别出心裁，在公式中使用“+”运算符：

=SUMPRODUCT(–(J2:J196=J209),L2:L196+R2:R196+U2:U196+V2:V196+Z2:Z196)

示例5：在单元格区域A1:A100中查找值为“good”的字符串的次数，但一些单元格包含引线或尾部空格，或者HTML非中断空格。

解决方案：基本的字符串统计是非常简单的，在公式中包含TRIM函数处理引线和尾部空格。但TRIM函数不能处理HTML非中断空格，这必须使用SUBSTITUTE函数从被测试的单元格区域中提取：

=SUMPRODUCT(–(TRIM(SUBSTITUTE(A1:A100,CHAR(160),””))=”good”))

示例6：统计单元格区域中唯一值的数量。

解决方案：如果整个单元格区域是A1:A20，那么公式为：

=SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20))

然而，如果单元格区域中任一单元格为空，那么会返回#DIV/0。公式应修改为：

=SUMPRODUCT((A1:A20<>””)/COUNTIF(A1:A20,A1:A20& “”))

最后，为了克服一个Bug，即COUNTIF/SUMIF的第1个参数的隐含交叉区域使用该参数的父工作表的已用区域，也会返回#DIV/0，可以使用下面的公式：

=SUMPRODUCT((A1:A20<>””)/(COUNTIF(A1:A20,A1:A20)+(A1:A20=””)))

示例7：统计两个条件分别满足两个不同单元格区域的实例。例如，单元格区域A1:A10中的内容是国家，B1:B10中是洲，C1:C10中是代表是否属于G7国家的标志。现在需要统计在欧洲或者是G7国的数量。

解决方案：要统计在欧洲或者是G7国的国家的数量，可以使用：

=SUMPRODUCT((B1:B10=”Europe”)+(C1:C10=”Y”))

问题是上面的公式将双倍统计处于欧洲并且也是G7国的国家，可以使用下面的公式解决：

=SUMPRODUCT((B1:B10=”Europe”)+(C1:C10=”Y”))-SUMPRODUCT(–(B1:B10=”Europe”),–(C1:C10=”Y”))

使用SUMPRODUCT函数计算在欧洲且是G7国的国家数，然后减去重复统计的国家数。

当然，还有另一种方法，即：

=SUMPRODUCT(–((B1:B10=”Europe”)+(C1:C10=”Y”)>0))

这里，允许公式作双倍的统计，但添加了进一步的测试来检查每一个结果是否大于0。因此，在里层的公式返回数据集{0;2;1;1;0;0;0;0;0;0}，测试该数据集中大于0的元素，得到数组{0;1;1;1;0;0;0;0;0;0}，然后将数组求和得到正确的结果。

示例8：统计给定区域中多于一个值的实例。例如，统计单元格区域A1:A10中有多少“Ford”和“Chrysler”。

解决方案：可以使用下面的公式：

=SUMPRODUCT((A1:A10=”Ford”)+(A1:A10=”Chrysler”))

或者：

=SUMPRODUCT(–(A1:A10={“Ford”,”Chrysler”}))

示例9：单元格区域A1:A10中是货物编码，B1:B10中是相应的存放位置，C1:C10是相应的货物数量，需要统计在特定的位置包含部分货物编码的项数。SUMIF函数可以使用通配符，但仅能进行一次测试，而SUMPRODUCT函数不直接支持通配符。

解决方案：可以使用FIND函数测试是否在货物编码里内嵌有部分编码，ISNUMBER函数用来测试是否找到了匹配的编码（避免出现#VALUE错误）

=SUMPRODUCT(–(ISNUMBER(FIND(“ATN”,A1:A10))),–(B1:B10=”Birmingham”),–(C1:C10))

FIND函数区分大小写，如果不需要区分大小写，那么使用SEARCH函数：

=SUMPRODUCT(–(ISNUMBER(SEARCH(“ATN”,A1:A10))),–(B1:B10=”Birmingham”),–(C1:C10))

示例10：统计两个日期之间某天的数量，排除其中的任何假期。

解决方案：SUMPRODUCT函数可以用来计算两个日期之间某天的数量。例如，假设在单元格A1和A2中分别存放着这两个日期，下面的公式返回这两个日期之间星期三的数量。

=SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(A1& “:” & A2)))=4))

这里利用了Excel以从1900年1月1日起的顺序号存储日期的原理，在INDIRECT函数中使用两个日期来“直接”在行中装载所有的日期，使用WEEKDAY函数和ROW函数来测试以确定这些日期中哪些是指定的日期。

NETWORKDAYS函数提供了一种在统计中排除假期的便利方法。此外，能够添加一个排除假期的测试来达到目的。假设假期存放在名称为holidays的命名单元格中，可以使用：

=SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(A1& “:” &A2)))=4),–(COUNTIF(holidays,ROW(INDIRECT($A$1 & “:”& A2)))=0))

我们也可以模拟NETWORKDAYS函数来统计两个日期之间的天数，排除星期六、星期日和假期，公式如下：

=SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(A1& “:” & A2)))<>1),(–(WEEKDAY(ROW(INDIRECT(A1& “:” &A2)))<>7)),–(COUNTIF(holidays,ROW(INDIRECT($A$1 & “:” & A2)))=0))

看起来似乎没有必要，因为我们可以更容易地使用NETWORKDAYS函数，但相比NETWORKDAYS函数来说，这里提供了一点小小的优势，不用管日期顺序。通过改变星期值，可以使用排除任何的1、2、3或其它天数，不只是星期六和星期天。

注：Excel 2010中已经引入了NETWORKDAYS.INTL函数解决了部分NETWORKDAYS函数的局限。

示例11：仅对匹配某条件的可见单元格求和。例如，对单元格区域B1:B100中含有值“North”的相对应的A1:A100中的值求和，由于在数据中应用了筛选，一些行不可见。

解决方案：公式的第一部分是在单元格区域B1:B100中直接测试是否含有值“North”并求单元格区域A1:A100中的相应的值的和：

–($B$1:$B$100=“North”),$A$1:$A$100

可见单元格的统计比较复杂，使用SUBTOTAL函数以及联合使用ROW、INDEX和OFFSET函数：

–(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$100,1,1),ROW($A$1:$A$100)-ROW(INDEX($A$1:$A$100,1,1))=1)

最终的公式如下：

=SUMPRODUCT(–(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$100,1,1),ROW($A$1:$A$100)-ROW(INDEX($A$1:$A$100,1,1))=1), –($B$1:$B$100=“North”),$A$1:$A$100)