excelperfect
本文主要探讨什么时候使用SUMPRODUCT函数更有效，而什么时候应该使用SUMIFS函数代替SUMPRODUCT函数。
下面是关于SUMPRODUCT函数使用的一些重要说明：
1. SUMPRODUCT函数接受两个或多个相同维数大小的数组作为参数，首先将数组相乘，然后将结果相加。
2. SUMPRODUCT函数接受的数组参数数量范围为1至255个，这些参数分别命名为array1、array2，等等，数组必须具有相同的大小（例如1×3和1×3，2×5和2×5，等等）。
3. SUMPRODUCT函数将非数字数据视为0。
4. SUMPRODUCT函数的数组参数可以处理数组操作以及由数组操作生成的结果数组，而无需按Ctrl+Shift+Enter键。
5. 如果需要相乘的数组的维数不同，那么可以使用乘法运算符并将相乘的数组放在单个数组参数中。
6. 当需要将数组操作的结果相加时，可以在参数array1中包含单个数组操作。此时，仅使用了SUMPRODUCT函数的SUM部分。
7. 在Excel 2003或更早版本中，SUMPRODUCT函数可用于的多条件求和和计数。
8. 由于SUMPRODUCT函数将非数字数据视为0，因此如果要在数组计算中使用TRUE和FALSE值，则必须将它们转换成1和0。可使用任何的数学运算来进行转换，但使用双减号通常是最快的计算方法。
9. 在Excel 2007及后续版本中，如果要进行多条件计数或求和，首先考虑是否可以使用SUMIFS函数或COUNTIFS函数，因为它们的计算速度更快。
10. SUMPRODUCT函数可用于处理工作簿引用，以替代SUMIF函数、COUNTIF函数、SUMIFS函数和COUNTIFS函数。
11. SUMPRODUCT函数可用于处理SUMIF函数、COUNTIF函数、SUMIFS函数和COUNTIFS函数的单元格区域（range）参数无法处理的数组计算。
示例：相同大小的两个或多个数组相乘，然后相加
SUMPRODUCT函数的基本用法是在其中输入以逗号分隔开的几个大小相同的单元格区域。SUMPRODUCT函数将相应的单元格相乘，然后将结果相加。如下图1所示，简洁的公式求出了两组单元格区域中相应单元格相乘并将乘积相加的结果。
图1
下图2展示了SUMPRODUCT函数与直接使用乘法运算符的SUM函数相比的优势。SUMPRODUCT函数不需要Ctrl+Shift+Enter，并且将0、空单元格和文本视为数字0；而SUM函数的数组公式结果是错误值#VALUE!，因为数字和文本不能直接相乘。
图2
下图3至图5展示了SUMPRODUCT函数基本用法的3个示例。
图3：根据权重计算成绩
图4：根据可能性预测订单数
图5：4个单元格区域相乘后的结果相加得到总压力
注意，如果SUMPRODUCT函数接受两个单元格作为其参数，但这两个单元格都为空，则结果是错误值，如下图6所示。
图6
示例：三个具有不同大小的单元格区域相乘，然后相加
如下图7所示，基于经济状态的概率（单元格区域B3:B5）、每支股票的权重（单元格区域C1:D1）以及单支股票收益估计来估算持有股票的预期收益，使用数组相乘操作来创建作为SUMPRODUCT函数参数的结果。
图7
注意，虽然示例公式使用数组相乘作为SUMPRODUCT函数的参数array1的值，但是由于相乘操作不能处理文件，因此要注意用于相乘的数组中不能含有文本值，否则公式会导致错误#VALUE!。
示例：将数组运算得到的结果数组相加（仅利用SUM部分）
如下图8所示，在ROUND函数中进行数组运算，然后使用SUMPRODUCT函数计算总和。注意，可以使用SUM函数，但需要按Ctrl+Shift+Enter输入数组公式，因此SUMPRODUCT函数更简单些。
图8
什么时候使用SUMPRODUCT函数进行多条件计数或求和
在Excel中，除SUMPRODUCT函数外，COUNTIFS函数、SUMIFS函数、DCOUNT函数和DSUM函数都可以进行多条件计数或求和，并且比SUMPRODUCT函数更有效率。但为什么还要使用SUMPRODUCT函数呢？下面是一些理由。
1. 在Excel 2003及以前的版本中，没有COUNTIFS函数和SUMIFS函数。
2. 在Excel 2003及以前的版本中，不总是可能去使用D-函数，因为它们需要合适的数据集，并且难以将公式复制到其它单元格。
3. 在使用Excel 2007及以后的版本时，可能会碰到在Excel 2007发布以前已经创建的带有SUMPRODUCT函数的公式的工作表。
4. SUMPRODUCT函数能够进行COUNTIFS函数和SUMIFS函数无法进行的一些多条件计算。
如下图9所示，要求使用公式求出员工Kip花在Project 2项目上的次数和时间和。示例中，使用了更有效率的COUNTIFS函数和SUMIFS函数。
在Excel 2007及以后的版本中，COUNTIFS函数和SUMIFS函数提供了以下优势：
1. 与SUMPRODUCT函数或等效的D-函数相比，使用COUNTIFS函数和SUMIFS函数的公式计算速度更快。对于大数据集来说，它们能够明显地缩短计算时间。
2. 不像D-函数，在数据集或判断条件区域中，它们不需要的字段名。
3. 不像D-函数，使用它们的公式很容易被复制到其他单元格。
图9
下图10展示了使用DCOUNT函数和DSUM函数获得次数和求和的示例。如果使用的是Excel 2003或以前的版本，在数据集和条件区域中带有字段名的合适的数据集，不需要复制公式到其它单元格，那么使用D-函数更有效率，公式的计算时间比SUMPRODUCT函数更快。此外，D-函数的公式比等价的SUMPRODUCT函数的公式更简洁，尤其是具有多个条件时。
图10
下图11展示了使用SUMPRODUCT函数获得次数和求和的示例。如果使用的是Excel 2003或以后的版本，在数据集或条件区域中没有字段名，SUMPRODUCT函数能够进行运算，但D-函数不能。
图11
当使用Excel 2003及以前版本时，下图12展示使用SUMPRODUCT函数比D-函数更有优势：可以复制公式。示例中，添加了两个条件并创建了交叉表，在单元格F3中创建公式后，向右向下复制到单元格区域F3:G5。
图12
使用双减号将TRUE和FALSE转换成1和0
首先，注意下面两个问题：
1. SUMPRODUCT函数将非数字数据视为0，它不认识TRUE和FALSE。
2. 任何数学运算将TRUE和FALSE转换为1和0。
如下图13所示，A2:A5=C2生成一个由逻辑值组成的数组，而SUMPRODUCT函数将逻辑值视为0，因此结果为0，而实际应该是2。
图13
通过对逻辑值执行任何数学运算将TRUE和FALSE转换为1和0，如下图14所示。
图14
下图15展示在SUMPRODUCT函数公式中如何使用不同的数学运算来统计列A中“Kip”的数量。
图15
下面详细给出了公式[1]的运算过程，让我们理解双减号的工作原理。
=SUMPRODUCT(--(A2:A5=C2))
转换为：
=SUMPRODUCT(--({TRUE;FALSE;TRUE;FALSE}))
转换为：
=SUMPRODUCT(-({-1;0;-1;0}))
转换为：
=SUMPRODUCT({1;0;1;0})
得到结果：
2
据测试，使用双减号比其它方法速度更快。
关于SUMIFS函数、DSUM函数和SUMPRODUCT函数中使用比较运算符的语法差异
在使用比较运算符时，SUMIFS函数、DSUM函数和SUMPRODUCT函数有不同的要求，如下图16至图19所示。
图16：SUMIFS函数接受包含比较运算符和要处理的数值的单元格作为条件。本示例中，要求在两个日期之间的条件，单元格A2中包含大于等于某日期的条件，单元格B2中包含小于等于某日期的条件。
图17：SUMIFS函数接受在公式中连接在一起的条件，示例在公式中将比较运算符（加上双引号）和单元格中的值相连接。
图18：DSUM函数要求在单元格中放置比较运算符，没有SUMIFS函数那么灵活。
图19：SUMPRODUCT函数要求将比较运算符直接放在数组和条件之间，以创建数组操作。这也是其运算时间较长的原因。
什么时候使用SUMPRODUCT函数是最好的
类似于SUMIFS函数、SUMIF函数、COUNTIFS函数等都包含一个参数range或一个含有条件值的单元格区域的参数criteria_range。参数range和criteria_range在任何情况下都不能处理数组。当使用工作簿引用，然后关闭这个含有外部数据的工作簿时，该工作簿引用将转换为数组并导致该函数显示#VALUE！错误。而SUMPRODUCT函数则不会受到影响。因此，如果公式中含有对外部工作簿的数据引用或者遇到上述函数不能处理数组的情形时，则最好使用SUMPRODUCT函数。
示例：计算两个日期之间有多少个星期五是13号
如下图20所示，使用SUMPRODUCT函数的公式求出在两个日期之间有多少个星期五是13号。公式利用了前面介绍的ROW函数生成连续数字的技巧，这些数字都代表日期的序号，再使用TEXT函数设置其日期格式并与指定格式的日期比较，求出该日期的数量。
图20
如果使用COUNTIF函数的公式：
=COUNTIF(TEXT(ROW(INDIRECT(B2&':'&B3)),'dddd')='Friday 13',TRUE)
Excel会弹出如下图21所示的警告消息。
图21
这个消息并没有指出公式存在的问题，其问题是：COUNTIF函数中的参数range不能处理数组或数组操作。在COUNTIF函数、SUMIF函数、AVERAGEIF函数、SUMIFS函数、COUNTIFS函数和AVERAGEIFS函数中，参数range和参数criteria_range不能够处理数组。这种情形下，使用SUMPRODUCT函数。
不能够处理数组（数组运算、数组常量、通过工作簿引用创建的数组）的函数参数：
1. VLOOKUP函数中的参数lookup_value。
2. HLOOKUP函数中的参数lookup_value。
3. SUMIF函数中的参数range。
4. COUNTIF函数中的参数range。
5. AVERAGEIF函数中的参数range。
6. SUMIFS函数中的参数criteria_range。
7. COUNTIFS函数中的参数criteria_range。
8. AVERAGEIFS函数中的参数criteria_range。
SUMPRODUCT函数参数里的IF函数
在前面的系列文章的讲解中，我们讲过一条规则：如果在IF函数中有数组运算，那么无论IF函数位于什么函数参数中，公式都需要按Ctrl+Shift+Enter键。如下图22所示，在单元格A5中没有按Ctrl+Shift+Enter键，结果是错误值#VALUE!。此外，为了避免潜在的歧义，在这种情形下可以使用单元格A10和A11中的公式。
图22
SUMPRODUCT函数特性小结
下面是SUMPRODUCT函数的一些重要特性：
1. 能够对相同大小的数组先相乘再相加。
2. 能够将数组运算的结果相加。（具有不同大小的数组，可能使用乘法运算和单个数组参数来得到结果）
3. 可以处理工作簿引用，而COUNTIF函数和COUNTIFS函数则不能。
4. 能够处理数组，而诸如COUNTIF和COUNTIFS函数中的参数range和criteria_range则不能处理数组。
5. 如果使用的是Excel 2007或以后的版本，那么对于多条件计算来说，使用COUNTIF、COUNTIFS及其它类似函数会比SUMPRODUCT函数更有效率。
6. 如果要进行多条件计算且不需要复制公式，那么使用D-函数可能比SUMPRODUCT函数更有效率。
7. 在SUMPRODUCT函数中使用IF函数的公式，必须按Ctrl+Shift+Enter键。为了避免误解，最好考虑使用其它公式。
《Ctrl+Shift+Enter：MasteringExcel Array Formulas》学习笔记
完美Excel
欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。
完美Excel社群2020.9.13动态
#电子书# 数学01：小平邦彦高中数学教材
#电子书# 数学02：MIT线性代数笔记
#电子书# 数学03：几何变换
联系客服
