搜索
Excel的SUM函数9种公式设置范例（由WORD格式转换，看起来更清晰明了）1、数组求和456014535905671156461128910418979178100115200902001011162019120211012521010022011513021510523012013522011024015016525014030020021530019040050051560049010001000101511009902000公式1：{=SUM((A2:A13100)*A2:A13)}2296公式1a：=SUMPRODUCT((A2:A13100)*A2:A13)2296[公式说明]：本公式为数组公式，可以对A2:A13区域中大于100的数据进行求和，而排除小于等于100的数据。输入公式时必须按【Ctrl+Shift+Enter】组合键结束，否则无法得到正确结果。[使用注意]：1、公式中G12:G21100部分表示求和条件，后跟实际求和区域G12:G21。如果有多个条件，可一并罗列出来。例如求大于100且小于115的数据之和，公式如下:=SUM(G12:G21100)*(G12:G21115)*G12:G21)。2、此数组公式只适用于单个区域求和，如果有多个区域，只能用多个SUM求和，然后相加。例如对G12:G21和H12:H21区域中大于100的数汇总，公式如下:=SUM(SUM((G12:G21100)*G12:G21),SUM((H12:H21100)*H12:H21))。3、对于SUM函数的数组公式，可以用SUMPRODUCT函数来代替，从而将数组公式转换成普通公式。例如本案例的公式用SUMPRODUCT函数后，普通公式如下:=SUMPRODUCT((G12:G21100)*G12:G21)。公式2：{=SUM((A2:A13100)*(A2:A13115)*(A2:A13))}211公式2a：=SUMPRODUCT((A2:A13100)*(A2:A13115)*A2:A13)211A2:A13大于100且小于115的数据之和公式3：{=SUM(SUM((A2:B13100)*A2:B13),SUM((C2:D13100)*C2:D13))}1074211604862公式3a：=SUMPRODUCT((A2:B13100)*A2:B13+(C2:D13100)*C2:D13)10742A2:B13与C2:D13大于100的数据之和公式4：{=SUM(SUM((A2:A13100)*A2:A13),SUM((C2:C13100)*C2:C13))}591591公式4a：=SUMPRODUCT((A2:A13100)*(A2:A13150)*A2:A13+(C2:C13100)*(C2:C13150)*C2:C13)591591A2:A13与C2:C13大于100且小于150的数据之和2、数据类型转换求和4560145359056951564611289104189791781001152009020010111620191202110125210100220公式：=SUM(B37,A36:A41,C36:C41,E36:E41)2604公式：=SUM(VALUE(B37),A36:A41,C36:C41,E36:E41)2699公式：=SUM(--(B37),A36:A41,C36:C41,E36:E41)2699公式：=SUM((B37)*1,A36:A41,C36:C41,E36:E41)2699公式：=SUM((B37)/1,A36:A41,C36:C41,E36:E41)2699[公式说明]：SUM函数用于对单元格区域的数据或者逻辑值、表达式进行求和，它有1-255个参数。鉴于本题的特殊性，公式也可以改为=SUM(区域1)，函数会忽略区域中的文本。[使用注意]：1、SUM函数有1-255个参数。参数可以是区域，也可以是表达式。如:=SUM(G5,10*25,G6*8)。2、如果是文本型数字参数，SUM函数是可以直接求和的；如果是引用单元格中的文本型数字(单元格的数字前添加半角单引号“'”),则求和时将忽略。例如B37的值是“'95”，那么以下公式的结果大不同。=SUM(10,95)—结果为105,=SUM(10,B37)—结果为10。3、如果一定要对单元格中文本型数字进行求和，可以将它转换成数值。转换方法包括多种:利用VALUE函数转换、利用--转换和*1、/1方式转换。例如:=SUM(10,VALUE(B37))、=SUM(10,--(B37))、=SUM(10,(B37)*1)3、多条件求和1（同列条件和数值，同“数组求和”公式2）45601453590569515646112100104189791781151152009020012011620191202300125210100220公式：{=SUM(((A54:A59100)+(A54:A59200))*A54:A59)}401条件1：A54：A59小于100，条件2：A54：A59大于200；然后求A54：A59中对应值的和公式：{=SUM(((A54:A59100)+(A54:A59=115)+(A54:A59200))*A54:A59)}516条件1：A54：A59小于100，条件2：A54：A59等于115，条件3：A54：A59大于200；然后求A54：A59中对应值的和[公式说明]：对于两个范围求和，即满足两个条件中任意一个条件都进行求和，这和同时满足两个条件才求和的公式思路不同。案例2的使用注意中已提到，多个条件同时满足才求和需要用*连接所有条件，表示同时满足多条件，而本案例中对多个条件用+来连接，表示对于多条例中满足任意一个条件就可以进行求和。本公式为数组公式。[使用注意]：1、多条件满足任意条件即求和，在设置公式时对于所有条件需要用+连接，如本例中(A54：A59100)+(C54：C59200),然后对条件用括号括起来再与实际求和区（E54：E59）相乘。如果缺少条件的外括号将得到错误结果。2、如果不只两个条件，也用同样方式将所有条件罗列出来并相加。例如求区域中小于100，大于200，等于115的数据和，那么数组公式如下:{=SUM(((A54:A59100)+(B54:B59=115)+(C54:C59200))*E54:E59)}4、多条件求和2（不同列条件和数值）姓名性别岗位职务工资张一男一车间高级工5000王二女一车间高级工4000李三TRUE一车间中级工3000赵四男二车间中级工2000周五女二车间中级工1000公式：{=SUM((B71:B75=男)*(C71:C75=一车间)*E71:E75)}5000公式：{=SUM((B71:B75&C71:C75=男一车间)*E71:E75)}5000条件1：男性职工，条件2：一车间，然后求工资和公式：{=SUM((B71:B75=男)*(C71:C75=一车间)*(D71:D75=高级工)*E71:E75)}5000公式：{=SUM((B71:B75&C71:C75&D71:D75=男一车间高级工)*E71:E75)}5000条件1：男性职工，条件2：一车间，条件3：高级工，然后求工资和公式：{=SUM((B71:B75=男)*(Sheet2!C2:C6=一车间)*(Sheet2!D2:D6=高级工)*E71:E75)}5000公式：{=SUM((B71:B75&Sheet2!C2:C6&Sheet2!D2:D6=男一车间高级工)*E71:E75)}5000条件1：男性职工，条件2：一车间，条件3：高级工，然后求工资和[公式说明]：本公式以H、I两列数据作为限制条件，对J列数据汇总。“(B71:B75=男)*(C71:C75=一车间)”表示同时满足两个条件，如果还有更多条件，也可以同时罗列出来，利用“*”符号连接，然后再与求和区相乘，得到最后的汇总结果。[使用注意]：1、“多条件求和1”是以求和数据自身为条件对目标区域进行求和，本案例以不同的区域作为限制，条件区有两个，对第三区域进行求和。在设置公式时，三个区域的单元格个数必须一致。例如以下公式将产生错误结果:{=SUM((B71:B75=男)*(C72:C75=一车间)*E71:E75)}。2、多条件求和时，条件录用区域与求和区域可以在不同列，三个区域只需要宽度、高度一致即可。它们所在的列可以不同，起止行也可以不同，甚至三个区域可以分别位于不同的工作表中，仍然可以得到需要的结果。例如条件区在Sheet2工作表，求和区在当前工作表，公式可以修改为:{=SUM((B71:B75=男)*(Sheet2!C2:C6=一车间)*(Sheet2!D2:D6=高级工)*E71:E75)}。3、本公式可以用另一种简化写法，仍然可以返回正确结果:=SUM((B71:B75&C71:C75=男一车间)*E71:E75)5、多工作表求和时表名称快捷输入公式：=SUM(Sheet2:Sheet5!E2:E6)60000求Sheet2:Sheet5!中E2:E6单元格的和[公式说明]：对多表相同区域求和，不需要录入每个工作表的求和地址，只需将第一个工作表名和最后一个表名用冒号连接，后跟!与单元格地址，以此作为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((B71:B75=男)*(D71:D75=中级工))}1求男性中级工人数[公式说明]：SUM函数虽然是求和函数，在很多时候可以用来计数。本例中利用H94:H101=男这一表达式返回一串包含TRUE和FALSE的数组，最后用*1将逻辑值转换成数值，并用SUM函数汇总。[使用注意]：1、表达式B71:B75=男得到的是包含逻辑值的数组。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；=SU