完美Excel公众号：excelperfect
本文资料来源于xlDynamic.com，供参考。
示例
下面将介绍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)
联系客服
