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

Excel的SUM函数9种公式设置范例1 、数组求和: {=SUM((G12:G21>100)*G12:G21)}［公式说明］：本公式为数组公式，可以对 G12:G21 区域中大于 100 的数据进行求和，而排除小于等于 100 的数据。

输入公式时必须按【 Ctrl+Shift+Enter 】组合键结束，否则无法得到正确结果。

［使用注意］：1、公式中 "G12:G21>100" 部分表示求和条件，后跟实际求和区域"G12:G21" 。

如果有多个条件，可一并罗列出来。

例如求大于 100 且小于 115 的数据之和，公式如下:=SUM(G12:G21>100)*(G12:G21<115)*G12:G21)。

2、此数组公式只适用于单个区域求和，如果有多个区域，只能用多个 SUM 求和，然后相加。

例如对 G12:G21 和 H12:H21 区域中大于100 的数汇总，公式如下:=SUM(SUM((G12:G21>100)*G12:G21),SUM((H12:H21>100)*H12:H21))。

3、对于 SUM 函数的数组公式，可以用 SUMPRODUCT函数来代替，从而将数组公式转换成普通公式。

例如本案例的公式用SUMPRODUCT 函数后，普通公式如下:=SUMPRODUCT((G12:G21>100)*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:J10,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。

3、如果一定要对单元格中文本型数字进行求和，可以将它转换成数值。

转换方法包括多种：利用VALUE函数转换、利用"--"转换和"*1"、”/1"方式转换。

例如 :=SUM(10,VALUE(G5))、 =SUM(10,--(G5))、 =SUM(10,(G5)*1) 3、多条件求和 1：{=SUM(((G23:G32<100)+(G23:G32>110))*G23:G32)}［公式说明］：对于两个范围求和，即满足两个条件中任意一个条件都进行求和，这和同时满足两个条件才求和的公式思路不同。

"案例 2"的使用注意 "中已提到，多个条件同时满足才求和需要用"*" 连接所有条件，表示同时满足多条件，而本案例中对多个条件用"+"来连接，表示对于多条例中满足任意一个条件就可以进行求和。

本公式为数组公式。

［使用注意］：1、多条件满足任意条件即求和，在设置公式时对于所有条件需要用"+"连接，如本例中 "(G23:G32<100)+(G23:G32>1 1 0)",然后对条件用括号括起来再与实际求和区相乘。

如果缺少条件的外括号将得到错误结果。

2、如果不只两个条件，也用同样方式将所有条件罗列出来并相加。

例如求区域中小于100 或者等于 120 的数据和，那么数组公式如下:=SUM(((G23:G32<100)+(G23:G32=110)+(G23:G32=120))*G23:G32) 4、多条件求和 2:{=SUM((H34:H43=" —车间")*(134:143="男"广J34:J43)}［公式说明］:本公式以H、I两列数据作为限制条件，对J列数据汇总。

“ (H34:H43="一车间")*(I34:I43=" 男")”表示同时满足两个条件，如果还有更多条件，也可以同时罗列出来，利用“ *”符号连接，然后再与求和区相乘，得到最后的汇总结果。

［使用注意］： 1、前三个案例是以求和数据自身为条件对目标区域进行求和，本案例以不同的区域作为限制，条件区有两个，对第三区域进行求和。

在设置公式时，三个区域的单元格个数必须一致。

例如以下公式将产生错误结果:=SUM((H34:H43=" 一车间 ")*(I34:I44=" 男"广J34:J43)、=SUM((H34:H43="—车间")*(135:144="男"广J34:J43)。

2、多条件求和时，条件录用区域与求和区域可以在不同列，三个区域只需要宽度、高度一致即可。

它们所在的列可以不同，起止行也可以不同，甚至三个区域可以分别位于不同的工作表中，仍然可以得到需要的结果。

例如条件区在Sheet2 工作表，求和区在当前工作表，公式可以修改为:=SUM((Sheet2!H34:H43=" —车间")*(Sheet2!l34:l43="男")*J34:J43)。

3、本公式可以用另一种简化写法，仍然可以返回正确结果 :=SUM((H34:H43&I34:I43=" 一车间男 ")*J34:J43)5、多工作表求和时表名称快捷输入：=SUM(附表一:附表五!B3:B10) ［公式说明］：对多表相同区域求和，不需要录入每个工作表的求和地址，只需将第一个工作表名和最后一个表名用冒号连接，后跟 "!" 与单元格地址，以此作为 SUM 函数的参数即可。

其中 "!"表示前面的字符为工作表名称。

［使用注意］： 1、本方法仅适用于每个工作表地址一致的情况，否则只能逐个输入地址再求和。

2、多表求和， SUM 函数的参数，工作表与单元格地址可以手动输入，也可以通过选择工作表中单元格地址来产生引用。

具体步骤如下：输入"=SUM("，然后选择待汇总的第一个工作表B3:B10区域，按住【Shift】键再单击工作表标签中待求和的最后一个工作表名，最后按【Enter】键结果结束即可。

3、如果工作表名中包含一些特殊字符，例如"!"、”@"，公式中的工作表名需要用单引号括起来，例如:=SUM('A组:!E组'!B3:B10)6、快速设置当前表以外的所有工作表相同区域的总和公式：=SUM('*'!B3)［公式说明］1 、公式中两个单引号中间加 "*" 表示除当前表以外的所有工作表。

2、在输入公式后，程序会自动将 "'*'"转换成实际的工作表名。

当按下【Enter】键后，公式将变成:=SUM(附表一：附表五!B3)［使用注意］1 、 "'*'" 只能手动输入，而不能用鼠标选择工作表来产生引用。

2、公式中 B3 必须使用相对引用。

3、本公式用法和 "案例 7"是不同的。

在本例中虽然也可以用"案例 7"的方法按住【Shift】键来选择需要求和的工作表产生引用，但是操作烦琐，特别是总表不在最后或者最前面，而是在待求和的工作表中间时，用"案例 7"的方法需要选择五次，而本例公式中的 "*" 可以自动的找到排除当前表之外的所有工作表数据。

也就是说"总表"不管在任何位置，都可以用同样的公式完成求和，而不需手动修改。

4、公式中 "*" 左右的单引号必须是半角符号。

7、用 SUM 函数计数：{=SUM((H94:H102="男")*(194:1102="汉"))} ［公式说明］： SUM 函数虽然是求和函数，在很多时候可以用来计数。

本例中利用 "H94:H101="男""这一表达式返回一串包含 TRUE 和FALSE的数组，最后用"*1"将逻辑值转换成数值，并用 SUM 函数汇总。

［使用注意］: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})—) 结果为错误值，因为 VALUE 函数无法转换成 TRUE。

8、求 1 累加到 100 之和： {=SUM(ROW(1:100))}［公式说明］:本公式利用 ROW函数返回1到100的序列，然后用 SUM函数将这个序列汇总得到 1—100的合计。

［使用注意］:1、ROW函数只能产生1到1048576之间的自然数(Excel2003版本为65536), 且不带小数。

但是通过换算也可以完成其他序列的求和。

例如1/1、1/2、1/3、1/4……1/99、1/100之和。

可以用以下公式：=SUM(1/R0W(1:100))。

如果是求 1开1次方，2开2 次方，3 开 3 次方至 100 开 100 次方的总和，那么可以用以下数组公式:{=SUM(ROW(1:100)A(1/ROW(1:100)))}。