Excel的SUM函数9种公式设置范例

1、Excel的SUM函数9种公式设置范例1 、数组求和:=SUM(G12:G21100)*G12:G21)公式说明 ：本公式为数组公式，可以对 G12:G21 区域中大于 100 的数据进行求和，而排 除小于等于 100 的数据。输入公式时必须按【 Ctrl+Shift+Enter 】组合键结束，否则无法得到 正确结果。使用注意 ：1、公式中 G12:G21100 部分表示求和条件，后跟实际求和区域G12:G21 。如果有多个条件，可一并罗列出来。例如求大于 100 且小于 115 的数据之和，公式如 下:=SUM(G12:G21100)*(G12:G21100)*G12:G21),SUM(H1

2、2:H21100)*H12:H21)。3、对于 SUM 函数 的数组公式，可以用 SUMPRODUCT函数来代替，从而将数组公式转换成普通公式。例如本 案 例 的 公 式 用 SUMPRODUCT 函 数 后 ， 普 通 公 式 如 下:=SUMPRODUCT(G12:G21100)*G12:G21。2 、 数 据 类 型 转 换 求 和 ： =SUM(VALUE(H5),H6:H10,J5:J10,L5:L10)； =SUM(- (H5),H6:H10,J5:J10,L5:L10)；=SUM(H5)*1,H6:H10,J5:J10,L5:L10)；=SUM(H5)/1,H6:H10,J5:J

3、10,L5:L10)公式说明 ：SUM 函数用于对单元格区域的数据或者逻辑值、表达式进行求和，它有1-255个参数。鉴于本题的特殊性，公式也可以改为=SUM(区域1)，函数会忽略区域中的文本。使用注意 ：1、 SUM 函数有 1-255 个参数。参数可以是区域，也可以是表达式。如:=SUM(G5,10*25,G6*8)。2、如果是文本型数字参数，SUM函数是可以直接求和的；如果是引用单元格中的文本型数字(单元格的数字前添加半角单引号“”),则求和时将忽略。例如 G5 的 值是 “95”， 那么以 下公式的 结果大不同 。 =SUM(10,95) 结果为 105,=SUM(10,G5)结果为10

4、。3、如果一定要对单元格中文本型数字进行求和，可以将它 转换成数值。转换方法包括多种：利用VALUE函数转换、利用-转换和*1、”/1方式转换。例如 :=SUM(10,VALUE(G5)、 =SUM(10,-(G5)、 =SUM(10,(G5)*1) 3、多条件求和 1：=SUM(G23:G32110)*G23:G32)公式说明 ：对于两个范围求和，即满足两个条件中任意一个条件都进行求和，这和同时 满足两个条件才求和的公式思路不同。 案例 2的使用注意 中已提到，多个条件同时满足 才求和需要用 * 连接所有条件，表示同时满足多条件，而本案例中对多个条件用+来连接，表示对于多条例中满足任意一个条

5、件就可以进行求和。本公式为数组公式。使用注意 ：1、多条件满足任意条件即求和，在设置公式时对于所有条件需要用+连接，如本例中 (G23:G321 1 0),然后对条件用括号括起来再与实际求和区相乘。 如果缺少条件的外括号将得到错误结果。2、如果不只两个条件，也用同样方式将所有条件罗列出来并相加。例如求区域中小于100或 者 等 于 120 的 数 据 和 ， 那 么 数 组 公 式 如 下:=SUM(G23:G32100)+(G23:G32=110)+(G23:G32=120)*G23:G32) 4、多条件求和 2: =SUM(H34:H43= 车间)*(134:143=男广J34:J43)公

6、式说明:本公式以H、I两列数据作为限制条件，对J列数据汇总。“ (H34:H43=一车间)*(I34:I43= 男 )”表示同时满足两个条件，如果还有更多条件，也可以同时罗列出来，利 用“ *”符号连接，然后再与求和区相乘，得到最后的汇总结果。 使用注意 ： 1、前三个案例是以求和数据自身为条件对目标区域进行求和，本案例以不同 的区域作为限制，条件区有两个，对第三区域进行求和。在设置公式时，三个区域的单元 格个数必须一致。例如以下公式将产生错误结果:=SUM(H34:H43= 一车间 )*(I34:I44= 男广J34:J43)、=SUM(H34:H43=车间)*(135:144=男广J34:

7、J43)。2、多条件求和时，条件录 用区域与求和区域可以在不同列，三个区域只需要宽度、高度一致即可。它们所在的列可 以不同，起止行也可以不同，甚至三个区域可以分别位于不同的工作表中，仍然可以得到 需要的结果。例如条件区在Sheet2 工作表，求和区在当前工作表，公式可以修改为:=SUM(Sheet2!H34:H43= 车间)*(Sheet2!l34:l43=男)*J34:J43)。3、本公式可以用另一 种简化写法，仍然可以返回正确结果 :=SUM(H34:H43&I34:I43= 一车间男 )*J34:J43)5、多工作表求和时表名称快捷输入：=SUM(附表一:附表五!B3:B10) 公式说明

8、 ：对多表相同区域求和，不需要录入每个工作表的求和地址，只需将第一个工 作表名和最后一个表名用冒号连接，后跟 ! 与单元格地址，以此作为 SUM 函数的参数即 可。其中 !表示前面的字符为工作表名称。使用注意 ： 1、本方法仅适用于每个工作表地址一致的情况，否则只能逐个输入地址再求和。 2、多表求和， SUM 函数的参数，工作表与单元格地址可以手动输入，也可以通过选 择工作表中单元格地址来产生引用。具体步骤如下：输入=SUM(，然后选择待汇总的第一个工作表B3:B10区域，按住【Shift】键再单击工作表标签中待求和的最后一个工作表名， 最后按【Enter】键结果结束即可。3、如果工作表名中包

9、含一些特殊字符，例如!、”，公式中的工作表名需要用单引号括起来，例如:=SUM(A组:!E组!B3:B10)6、快速设置当前表以外的所有工作表相同区域的总和公式：=SUM(*!B3)公式说明 1 、公式中两个单引号中间加 * 表示除当前表以外的所有工作表。 2、在输入公 式后，程序会自动将 *转换成实际的工作表名。当按下【Enter】键后，公式将变成:=SUM(附表一：附表五!B3)使用注意 1 、 * 只能手动输入，而不能用鼠标选择工作表来产生引用。2、公式中 B3 必须使用相对引用。3、本公式用法和 案例 7是不同的。在本例中虽然也可以用案例 7的方法按住【Shift】键来选择需要求和的工

10、作表产生引用，但是操作烦琐，特别是总表不在 最后或者最前面，而是在待求和的工作表中间时，用案例 7的方法需要选择五次，而本例公式中的 * 可以自动的找到排除当前表之外的所有工作表数据。也就是说总表不管在任何位置，都可以用同样的公式完成求和，而不需手动修改。4、公式中 * 左右的单引号必须是半角符号。7、用 SUM 函数计数：=SUM(H94:H102=男)*(194:1102=汉) 公式说明： SUM 函数虽然是求和函数， 在很多时候可以用来计数。 本例中利用 H94:H101=男这一表达式返回一串包含 TRUE和FALSE的数组，最后用*1将逻辑值转换 成数值，并用 SUM 函数汇总。使用注

11、意:1、表达式H94:H101=男得到的是包含逻辑值的数组。SUM函数可以对逻辑参数进行求和，但是包含于数组中的逻辑值值却会被忽略。例如以下公式:=SUM(TRUE,1)结果为2，=SUM(TRUE,1结果为1。但是通过*1将逻辑值转换成数值后就可以直接求和 了。例如:=SUM(TRUE,1*1)结果为2。2、将逻辑值转换成数值，除了用*1之外，也可以改用-或者/1等手法，但是用 VALUE函数却不行，它只能将文本型数字转换成数值。例 如 :=SUM(-(TRUE,1) 结 果 等 于 2 ； =SUM(TRUE,1)/1) 结 果 等 于 1 ； =SUM(VALUE(TRUE,1) 结果为

12、错误值，因为 VALUE 函数无法转换成 TRUE。8、求 1 累加到 100 之和： =SUM(ROW(1:100)公式说明:本公式利用 ROW函数返回1到100的序列，然后用 SUM函数将这个序列汇 总得到 1100的合计。使用注意:1、ROW函数只能产生1到1048576之间的自然数(Excel2003版本为65536), 且不带小数。但是通过换算也可以完成其他序列的求和。例如1/1、1/2、1/3、1/41/99、1/100之和。可以用以下公式 ：=SUM(1/R0W(1:100)。如果是求 1开1次方，2开2 次 方 ，3 开 3 次 方 至 100 开 100 次 方 的 总和 ，

13、 那 么 可 以 用 以 下 数 组 公式:=SUM(ROW(1:100)A(1/ROW(1:100)。如果是对1 100之间的奇数进行求和，可以用以 下数组公式：=SUM(ROW(1:50)*2-1)。2、ROW(1:100)产生的结果是一个包含1到100之间的自然数数组。用SUM函数对这种数组求和时必须用数组公式，否则只能对数组中的第一个数值进行求和。如果将 ROW(1:100)写成1,2,3,4这种形式的数组则可以按照普通公式 处理。9、多个工作表相同区域求前三名产量之和：=SUM(LARGE(CHOOSE(1,2,3,4,5附表 一!B3:B10,附表二！B3:B10,附表三！B3:B10,附表四！B3:B10,附表五！B3:B10),ROW(1:3)公式说明:如以下公式:=SUM(LARGE附表一：附表五！B3:B13,ROW(1:3)