详解excel的SUMPRODUCT函数

原创

捷后愚生 2024-10-18 17:29:04 博主文章分类：效率工具 ©著作权

©著作权归作者所有：来自51CTO博客作者捷后愚生的原创作品，请联系作者获取转载授权，否则将追究法律责任

SUMPRODUCT函数是Excel中一个非常强大的函数，它可以在给定的几组数组中，将数组间对应的元素相乘，然后返回这些乘积之和。下面将详细介绍SUMPRODUCT函数的语法、用法以及通过具体例子来展示其应用。

SUMPRODUCT函数的基本语法为：

=SUMPRODUCT(array1, [array2], [array3], … [arrayN])

其中，array1是必需的参数，表示要进行乘法运算的第一个数组；[array2]、[array3]等是可选参数，表示要进行乘法运算的其他数组。这些数组可以是区域引用，也可以是数组常量。

常规用法：
- 计算两个数组对应元素的乘积之和。例如，假设A1:A3区域包含数值{1,2,3}，B1:B3区域包含数值{4,5,6}，则公式=SUMPRODUCT(A1:A3, B1:B3)将返回1*4+2*5+3*6=32。
条件求和：
- 使用布尔值（TRUE/FALSE）作为条件，与其他数组相乘，从而实现条件求和。TRUE在运算中可视为1，FALSE可视为0。例如，假设有一个销售数据表，其中包含产品名称、销售额等信息，要计算所有“水果”类产品的销售额总和，可以使用公式=SUMPRODUCT((产品名称列="水果")*(销售额列))。
多条件求和：
- 在单条件求和的基础上，增加额外的条件。例如，要计算某个门店中“水果”类产品的销售额总和，可以使用公式=SUMPRODUCT((门店列="某门店")(产品名称列="水果")(销售额列))。
计数：
- 通过将条件数组与逻辑值1或0相乘，并利用SUMPRODUCT函数求和，可以实现计数功能。例如，要统计某个类别中的不同产品种类数，可以使用公式=SUMPRODUCT(--(产品名称列=某个类别))。这里的双负号（--）用于将逻辑值TRUE/FALSE转换为1/0。
与其他函数组合使用：
- SUMPRODUCT函数还可以与其他函数组合使用，以实现更复杂的计算。例如，与MONTH函数组合使用，可以计算特定月份的销售总额；与ISNONTEXT函数组合使用，可以实现指定条件的求和等。

假设有一个销售数据表，包含以下信息：

计算所有门店的水果销售额总和：

=SUMPRODUCT((B2:B7="水果")*(D2:D7))
计算一店在2023-01-01至2023-01-05期间的销售额总和：

=SUMPRODUCT((A2:A7="一店")(C2:C7)((D2:D7>="2023-01-01")*(D2:D7<="2023-01-05")))

注意：在这个例子中，由于条件数组（D2:D7>="2023-01-01"和D2:D7<="2023-01-05"）的结果为布尔值，它们相乘后得到的结果也是一个布尔值数组，这个数组再与销售额数组C2:C7相乘，最后由SUMPRODUCT函数求和。
统计不同产品的种类数：

=SUMPRODUCT(--(B2:B7<>"")/COUNTIF(B2:B7, B2:B7&""))

注意：这个例子的公式较为复杂，它使用了数组公式和COUNTIF函数相结合的方法来统计不同产品的种类数。这里的B2:B7<>""用于排除空值，COUNTIF函数用于计算每个产品出现的次数，然后通过SUMPRODUCT函数求和得到不同产品的种类数（由于存在重复产品，所以不能直接使用SUMPRODUCT(--(B2:B7<>""))来计算）。不过，这个公式并不是SUMPRODUCT函数的典型用法，且在新版本的Excel中可能有更简洁的替代方案（如使用UNIQUE函数等）。在实际应用中，应根据具体情况选择合适的公式和函数。