《SUMPRODUCT函数用法详解》由会员分享，可在线阅读，更多相关《SUMPRODUCT函数用法详解（11页珍藏版）》请在金锄头文库上搜索。
1、Excel 多条件求和 & SUMPRODUCT 函数用法详解转Excel 多条件求和 & SUMPRODUCT 函数用法详解龙逸凡日常工作中，我们经常要用到多条件求和，方法有多种，第一类：使用基本功能来实现。主要有：筛选、分类汇总、数据透视表、多条件求和向导；第二类：使用公式来实现方法。主要有：使用 SUM 函数编写的数组公式、联用 SUMIF 和辅助列（将多条件变为单条件） 、使用 SUMPRODUCT 函数、使用 SUMIFS 函数(限于 Excel2007 及以上的版本) ，方法千差万别、效果各有千秋。本人更喜欢用 SUMPRODUCT 函数。由于 Excel 帮助对SUMPRODUC
2、T 函数的解释太简短了，与 SUMPRODUCT 函数的作用相比实在不匹配，为了更好地掌握该函数，特将其整理如下。龙逸凡注：欢迎转贴，但请注明作者及出处。一、 基本用法在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。语法：SUMPRODUCT(array1,array2,array3, .)Array1, array2, array3, . 为 2 到 30 个数组，其相应元素需要进行相乘并求和。公式：=SUMPRODUCT(A2:B4, C2:D4)A B C D1 Array 1 Array 1 Array 2 Array 22 3 4 2 73 8 6 6 74 1 9 5
3、3公式解释：两个数组的所有元素对应相乘，然后把乘积相加，即 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3。计算结果为 156二、 扩展用法1、 使用 SUMPRODUCT 进行多条件计数语法：SUMPRODUCT(条件 1)*(条件 2)*(条件 3)* (条件 n)作用：统计同时满足条件 1、条件 2 到条件 n 的记录的个数。实例：=SUMPRODUCT(A2:A10=男)*(B2:B10=中级职称)公式解释：统计性别为男性且职称为中级职称的职工的人数2、 使用 SUMPRODUCT 进行多条件求和语法：SUMPRODUCT(条件 1)*(条件 2)* (条件 3)
4、*(条件 n)*某区域)作用：汇总同时满足条件 1、条件 2 到条件 n 的记录指定区域的汇总金额。实例：=SUMPRODUCT(A2:A10=男)*(B2:B10=中级职称)*C2:C10)公式解释：统计性别为男性且职称为中级职称的职工的工资总和（假设 C 列为工资）赞 42010-12-20 00:01 回复 catyoung18 4 位粉丝 2 楼三、 注意事项1、数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。 2、SUMPRODUCT 函数将非数值型的数组元素作为 0 处理。3、在 SUMPRODUCT 中，2003 及以下版本不支持整列（
5、行）引用，必须指明范围，不可在 SUMPRODUCT 函数使用 A:A、B:B，Excel2007 及以上版本可以整列（列）引用，但并不建议如此使用，公式计算速度慢。4、SUMPRODUCT 函数不支持“*”和“？”通配符SUMPRODUCT 函数不能象 SUMIF、COUNTIF 等函数一样使用“*”和“？”等通配符，要实现此功能可以用变通的方法，如使用 LEFT、RIGHT、ISNUMBER(FIND()或ISNUMBER(SEARCH()等函数来实现通配符的功能。如：=SUMPRODUCT(A2:A10=男)*(B2:B10=中级职称)*(LEFT(D2:D10,1)= 龙)*C2:C1
6、0)=SUMPRODUCT(A2:A10=男)*(B2:B10=中级职称)*(ISNUMBER(FIND(龙逸凡,D2:D10)*C2:C10)注：以上公式假设 D 列为职工姓名。 ISNUMBER(FIND()、ISNUMBER(SEARCH() 作用是实现“*”的通配功能，只是前者区分大小写，后者不区分大小写。5、SUMPRODUCT 函数多条件求和时使用“， ”和“*”的区别：当拟求和的区域中无文本时两者无区别，当有文本时，使用“*”时会出错，返回错误值 #VALUE!，而使用“， ”时 SUMPRODUCT 函数会将非数值型的数组元素作为 0 处理，故不会报错。 也就是说：公式 1：=
7、SUMPRODUCT(A2:A10=男)*(B2:B10=中级职称)*C2:C10)公式 2：=SUMPRODUCT(A2:A10=男)*(B2:B10=中级职称)，C2:C10)当 C2:C10 中全为数值时，两者计算结果一样，当 C2:C10 中有文本时公式 1 会返回错误值 #VALUE!，而公式 2 会返回忽略文本以后的结果。四、 网友们的精彩实例1、求指定区域的奇数列的数值之和=SUMPRODUCT(MOD(COLUMN(A1:F1),2)*A1:F1)2、求指定区域的偶数行的数值之和 =SUMPRODUCT(MOD(ROW(A1:A22),2)-1)*A1:A22)*(-1)3、求
8、指定行中列号能被 4 整除的列的数值之和 =SUMPRODUCT(MOD(COLUMN(A1:P1),4)=0)*A1:P1)4、.求某数值列前三名分数之和SUMPRODUCT(LARGE(B1:B16,ROW(1:3)5、统计指定区域不重复记录的个数SUMPRODUCT(1/COUNTIF(V11:V15,V11:V15)QUOTE:以下是引用 tonycq 在 2008-1-3 17:32:46 的发言：这里我仅谈该函数的两个关键问题未涉及其它查找函数，感谢 gdliyy 版主对文中不妥之处的指正。针对您前面对 SUMPRODUCT 的描述，我再补充以下几点，欢迎大家共同讨论！3、对于 S
9、UMPRODUCT 函数，原来曾经有些讨论，这里再补充一下：a)如果所有参数使用逗号分隔，那么限制就比较多，要求如下：（以下简单以引用为例）一维引用的维度必须相同，即：sumproduct(a1:a5,b1:f1)，结果错误，因为一维引用的方向不同；相同维度引用的尺寸大小必须相同，即：sumproduct(a1:a5,b1:b6)，结果错误，因为前一引用为 5 个元素，后一引用为 6 个元素；二维引用由于涉及一个平面，因此遵循第 2 个原则尺寸相同：即：sumproduct(a1:b5,c2:d6)，必须都是 5 行 2 列，而不是原 LZ 说的，只要面积相同就可行，如下面的公式结果错误：su
10、mproduct(a1:b5,c1:g2)，因为前引用为 5 行 2 列，而后引用为2 行 5 列，虽然单元格总个数相同，但实际的引用尺寸是完全不同的。b)如果函数中不用逗号分隔，而使用连乘方式来进行条件求和，那就遵循另外的规则：（仍以引用为例）两个不同维度的一维引用可以运算，即：sumproduct(a1:a5*b1:f1)，结果是对生成的 5 行 5 列的新数组进行求和；相同维度引用的尺寸大小必须相同，同数组相乘运算法则，即：sumproduct(a1:a5*b1:b6)，因为引用大小不同，导致最后一行单元格相乘得出#N/A 错误。同样的，如果两个二维引用相乘，也是遵循数组运算的法则，两个
11、引用的尺寸大小必须相同，即：sumproduct(a1:b5*c2:d6)，结果为分别对应相乘后的 5 行 2 列的新数组进行求和，而 sumproduct(a1:b5*c1:g2)的结果为错误，因此两引用尺寸不同。最后，这类数组（引用）的运算还可以综合应用，也是正确的，如：=SUMPRODUCT(a2:a6*b1:c1*d2:e6)参数内部运算结果最终将得到一个新的数组，即：5 行 2 列，最终由 SUMPRODUCT 求和得出结果。最后补充一点：对于 SUMPRODUCT 函数的用法有很多技巧，利用数组（引用）相乘运算，可以避免维度不同的错误；但利用逗号分隔的用法，可以避免文本参与乘法运算
12、出现错误，大家应该综合考虑来进行运用。如：统计各部门男性员工的工资总额范例：=Sumproduct(部门=指定部门)*(性别= 男), 工资)这样就可以避免很多问题，容错性也好得多。SUMPRODUCT函数目录含义 语法 说明 示例编 辑 本 段 含 义汉 语 的 意 思 是 SUM:【 数 】 求 和 PRODUCT:【 数 】 (乘 )积 20 is the product of 5 and 4.二 十 是 五 与四 的 乘 积 。 SUMPRODUCT:组 合 的 汉 语 意 思 是 :乘 积 之 和 在 给 定 的 几 组 数 组 中 ， 将 数 组 间 对 应 的 元 素 相 乘 ，
13、 并 返 回 乘 积 之 和 。 编 辑 本 段 语 法SUMPRODUCT（ array1,array2,array3, .） Array1,array2,array3, . 为 2 到 30 个 数 组 ， 其 相 应 元 素 需 要 进行 相 乘 并 求 和 。 编 辑 本 段 说 明 数 组 参 数 必 须 具 有 相 同 的 维 数 ， 否 则 ， 函 数 SUMPRODUCT 将 返 回 错误 值 #VALUE!。 函 数 SUMPRODUCT 将 非 数 值 型 的 数 组 元 素 作 为 0 处 理 。 编 辑 本 段 示 例如 果 将 示 例 复 制 到 空 白 工 作 表
14、中 ， 可 能 会 更 易 于 理 解 该 示 例 。 B C D E1 Array1 Array1 Array2 Array22 3 4 2 73 8 6 6 74 1 9 5 3公式 说明（结果）=SUMPRODUCT(B2:C4*D2:E4)两个数组的所有元素对应相乘，然后把乘积相加，即 3*2+4*7+8*6+6*7+1*5+9*3。(156)数 学 函 数 SUMPRODUCT 应 用 实 例 一 、 基 本 功 能 1 函 数 SUMPRODUCT 的 功 能 返 回 相 应 的 区 域 或 数 组 乘 积 的 和 。 2 基 本 格 式 SUMPRODUCT（ 数 据 1， 数
15、据 2， ， 数 据 30） 3 示 例数 据 表 A 列 B 列 C 列 D 列 E 列 数 据 1 数 据 2 数 据 3 数 据 4 数 据 52 3 4 12 105 5 6 5 209 7 8 #N/A 307 2 7 9 KL1 6 2 8 2 基 本 计 算 区 域 计 算 要 求 ： 计 算 A、 B、 C 三 列 对 应 数 据 乘 积 的 和 。 公 式 ：=SUMPRODUCT(A2:A6,B2:B6,C2:C6)计 算 方 式 ：=A2*B2*C2+A3*B3*C3+A4*B4*C4+A5*B5*C5+A6*B6*C6 即 三 个 区 域A2:A6,B2:B6,C2:C6 同 行 数 据 积 的 和 。 返 回 值 788。 数 组 计 算 要 求 ： 把 上 面 数 据 表 中 的 三 个 区 域 A2:A6,B2:B6,C2:C6 数据 按 一 个 区 域 一 个 数 组 ， 计 算 对 应 数 组 积 的 和 。 把 A2:A6,B2:B6,C2:C6 分别 作
