求和函数是我们平时使用的比较多的函数，前面两篇已经介绍了SUM及SUMIF函数的使用，本篇文章我们将继续介绍求和函数SUMPRODUCT。

一、SUMPRODUCT基本语法

功能：返回相应的区域或数组乘积的和。

基本格式：=SUMPRODUCT（array1，array 2，……，array 255），各个数组必须是相同的大小，否则会返回#VALUE！错误。

计算原理：两个数组的所有元素对应相乘，然后把乘积相加，例如：

SUMPRODUCT(a2:a5,b2:b5)的计算过程为=a2*b2+a3*b3+a4*b4+a5*b5=2*4+5*2+3*3+6*2=39。

二、SUMPRODUCT使用示例

示例一：根据绩效考核系数和各项评分求员工综合得分

某公司的员工综合考评得分由勤奋态度、业务工作、管理监督、指导协调、工作效果五项组成，每项的系数分别为0.1、0.35、0.15、0.15、0.25，请根据以下员工的各项评分计算出员工的综合得分。

根据以上对SUMPRODUCT函数的介绍，可以知道我们可以使用该函数比较简便的计算出总得分。公式如下：

=SUMPRODUCT($B$3:$F$3,B4:F4)

计算后的结果如下：

示例二：根据多条件求教师的课时总数

根据以下的教师课时安排表统计出性别为男且职称为中一的教师的总课时数。

该计算案例比上面的一个案例相对就要复杂很多，这里涉及到一个判断的问题。这里需要回顾我们之前介绍过的逻辑值的相关问题。然后再根据逻辑值的相关运算，就可以组成一个新的数组，将这个新的数组作为第一个数组条件。公示如下：

=SUMPRODUCT((B2:B16="男")*(C2:C16="中一"),D2:D16)。

这里对第一个条件进行一下运算的解释：

B2:B16="男"：结果为{true,true,false,false,true,true,false,false,false,true,false,false,true,false,false}

C2:C16="中一"：结果为{false,false,true,true,true,false,true,false,true,false,true,true,true,true,false}

(B2:B16="男")*(C2:C16="中一")：运算结果为{false,false,false,false,true,false,false,false,false,false,false,false,true,false,false}。

这个可能稍微有点复杂，大家好好的理解一下，这样对理解逻辑运算及该函数都会有很大的帮助。

三、SUMPRODUCT可能出现的错误

①编辑公式时，引用的数据区域大小不一致导致计算错误，返回值为#VALUE！。

②数据区域中有错误值时，计算出现错误值。

③数据区域引用不能整列引用，否则返回错误值#NUM！。

④数据区域有文本，计算中系统默认文本值为0。

四、拓展训练

大家思考一下，通过使用SUM函数的数组公式形式能不能实现上面的两个示例的计算？如果你已经想出计算方法，请评论发出来，如果你不知道怎么计算，请继续关注下篇文章，我将给大家讲解如何通过SUM数组函数进行计算。

求和的解法在Excel中是千变万化的，也有很多种方法，但是有一种问题，不知道各位小伙伴们有没有注意到，这也是一位朋友在工作中遇到的一个例子。

下面就给大家举例说明一下这个题目，如何用公式来完成。

问题：求下面三个型号的数量的合计。

关于以上题目的做法，很多朋友也说出了一些基本操作的方法，但是好像都不能奏效。

小A说：可以用透视表的作法。小编在这里拉了一个透视表，但是只出现了大写的型号的合计，而小写的也没有出现。并且在统计的金额上也出现了错误。

如下图所示：

小B说：可以用合并计算的方法。但是好像也没有起到作用，合并计算出来的结果与透视表的结果是一样的，当你去逐一验证的时候又是不对，也没有出现小写的型号。

小C说：再不行的话就用筛选的方法进行原始的合并计算。小编验证了一下，好像也是不行的。

小D说：那就是用sumif的基本用法进行条件求和。小编同样满足了他的要求，但是求和的结果也同上面的是一样的。

上面的众多的方法都尝试过了，但都不是最好的办法，那么说没有好的方法了吗？

并不是的，条条大路通罗马嘛，方法还是有的哈，听小编给大家一一讲来。

大家都知道，Sum、Sumif、Sumifs、Sumproduct在单独使用的时候是不区分英文字母的大小写的，包括之前讲过的Vookup也是一样的。所以大家在使用的时候一定要注意一些单独的字符或者字符串在查找或者求值的过程中要注意区分大小写。在Excel中，基本区分大小写的函数有Find、Exact、Substitute等函数。

下面，我们就使用这些函数进行求和。

方法一：Sum/Sumproduct+Exact组合法

在G5单元格中输入以下的公式：

=SUM(EXACT(F5,$A$2:$A$21)*($B$2:$B$21))

并按组合键确定并填充。

此处也可以将Sum替换成Ssumproduct，直接按Enter键即可。

解析：Exact可以区分大小写。先用此函数将目标查找值与查找区域进行对比，确定符合条件的区域值，EXACT(F5,$A$2:$A$21)返回值为：

{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}，在前几期中过，逻辑值是可民认参与运算的，此处就转换为了0，1的值，然后再将数量区域乘上，产生

=SUM({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}*{85;37;35;24;72;80;21;43;6;24;73;62;50;13;61;52;47;53;80;8})，然后再用sum函数将以上的相乘的值进行相加。

方法二：Sum/Sumproduct+Substitute组合法

=SUM((SUBSTITUTE($A$2:$A$21,F5,"")="")*($B$2:$B$21))

此处也可以将Sum替换成Sumproduct，直接按Enter键即可。

解析：Substitute是替换函数，此处使用该函数将要查找的目标值替换为空白，然后以此为条件进行逻辑值运算，从面得出结果。SUBSTITUTE($A$2:$A$21,F5,"")的执行结果为：

{"";"AB002";"AB003";"AB004";"AB005";"AB006";"AB007";"Ab001";"Ab002";"Ab003";"Ab004";"Ab005";"Ab006";"Ab007";"ab001";"";"ab003";"ab004";"ab005";"ab006"}，然后再将“=“” ”得出逻辑值为：

{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},然后同以上方法中的运算一样，进行求积求和运算即可。

遗留问题：关于在筛选状态下、合并计算求和、透视表求和会出现错误的结果？

以上问题供各位小伙伴自己去解决，这里不再做过多的阐述。

End.

来自：Excel学堂

小编今天给大家介绍一下如何计算时长，时长和的问题主要聚焦在1）分钟和时间转换问题2）如何提取时间数据3）处理异常数据如空值等等。来看下面示例：

解决步骤：

1.提取小时数据：

E3=VALUE(IFERROR(LEFT(D3,FIND("小",D3,1)-1),0))

解释为：FIND函数找“小”字在D3字符串的位置，减去1,作为左侧小时的位数，LEFT函数截取左侧的小时数，如果不存在小时或者没有记录则用IFERROR函数用0表示，最后用VALUE函数把截取到的数字字符串转换为数字格式。

2.提取分钟数据：

F3=VALUE(IFERROR(MID(D3,IFERROR(FIND("时",D3,1),0)+1,FIND("分",D3,1)-IFERROR(FIND("时",D3,1),0)-1),0))

解释为：分别用FIND函数找“分”和“时”在D3中的位置。找到后用MID函数截取分钟数，起始位置就是FIND函数找“时”加上1，如果FIND“时”函数错误，用IFERROR把错误值变成0，同理截取的字符串个数就是FIND“分”的位置-FIND“时”的位置再减去1，然后套个IFERROR来排除空值错误显示为0，最后套个VALUE函数转变为数字。

3.统一单位为分钟：

G3=E3*60+F3

解释为小时*60+分钟

4.求和时长

H3=CONCATENATE(INT(SUM(G3:G18)/60),"小时",MOD(SUM(G3:G18),60),"分钟")

解释为：先把统一后的分钟数求和，然后除以60在向下取整作为小时数,再把除以60的余数作为分钟数，用到MOD函数求余数。最后用CONCATENATE函数连接，显示格式为：数字+小时+数字+分钟

当然最后还可以不用统一为分钟，去掉G列，后面总小时数为E列的求和加上F列求和除以60的向下取整，然后F列的余数作为分钟数。

自动求和功能：在Excel“编辑”功能区，清除功能的上方有一个“自动求和”，可以紧接所选单元格之后显示所选单元格的求和值。

注：点击求和功能右边的黑色小三角，还会有平均值，计数值，最大值和最小值。意义就是：紧接所选单元格之后显示所选单元格的平均值（计数值、最大值、最小值）

一、 求和功能。

选择你需要求和的单元格，点击自动求和下拉按钮，选择求和，求和值会自动计算到选定单元格之后的一个单元格。

注：平均值、计数值等功能和自动求和功能一样（且在Excel右下角选定单元格时会出现平均值、计数值以及求和值。）

二、 多行（列）自动求和的使用。

A、选定需要求和的单元格，再多选择一个单元格容纳计算值，点击自动求和下拉菜单，选择你需要计算的功能，点击即可。

注：

1、 一定要多拉一个空白单元格，否则自动求和功能默认为是按列计算，计算值会出现在这列的第一个空白单元格内。

2、 利用快捷键“Alt”+“=”也可以实现上述自动求和（只能用于求和）

三、 选择多个指定单元格进行求和。

我们在使用Excel过程中，也可能遇到同列表格中，隔几行求一次和的情况，这种情况该如何？

按住“Ctrl”+“你需要求和的单元格”，然后点击自动求和下拉菜单，选择求和即可。

在工作中，有时候excel表格需要每天统计数据，表格的行数会随着每天记录的数据而不断增加。像下表，每天都要记录出库数量，并且每天都要统计出库合计数量。当每天新增记录时，出库合计数量不需要再重新计算，根据新增的记录数据自动求和。

今天的小技巧运用到OFFSET函数和COUNTA函数。

OFFSET函数：以指定的引用为参照系，通过给定的偏移量返回新的引用。

函数语法：OFFSET(reference, rows, cols, [height], [width])

这个函数有5个参数：

第一个参数是基点。

第二个参数是要偏移几行，正数向下，负数向上。

第三个参数是要偏移几列，正数向右，负数向左。

第四个参数是新引用几行。

第五个参数是新引用几列。

第一至第三个参数为必需，第四个和第五个参数为可选。

例如公式：=OFFSET(B2,3,2,3,2)

就是以B2为基点，向下偏移3行，向右偏移2列，新引用的行数是3行，新引用的列数是2列，最终得到对D5:E8区域的引用。和SUM函数组合=SUM(OFFSET(B2,3,2,3,2))就是对D5:E8区域求和。

COUNTA函数：计算区域中不为空的单元格的个数。

接下里求出库数量合计，在B1单元格输入公式：=SUM(OFFSET(C3,0,0,COUNTA(C:C)-1,1))。当我们再次想数据表格中增加数据时，就会自动统计出库数量。

如果想求出每一种类的数据和，不一定要通过筛选命令，当然用筛选也没有问题

至少有三个命令，可以实现每种类数据的求和

1.sumif

根据案例中，我们想求出黑龙江省的销售总额，我们该怎么做？

SUMIF 在 EXCEL 中被用来按条件进行求和。

语法

SUMIF(range,criteria,sum_range)

range:条件区域，笼统的来讲就是需要判断条件的区域

criteria:条件，这里可以用等于、大于、小于，可以比对各种数值类型

sum_range:求和的区域

我们看到黑龙江的销售额，就被轻松的求出来了

2.SUBTOTAL

首先需要在数据命令下，对数据进行筛选，筛选完成后，使用SUBTOTAL

SUBTOTAL函数语法：SUBTOTAL(function_num, ref1, ref2, ...)

第一参数：Function_num为 1 到 11（包含隐藏值）或 101 到 111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。

第二参数：Ref1、ref2为要进行分类汇总计算的 1 到 254 个区域或引用。

SUBTOTAL函数在筛选命令，对数据处理的功能是很强大的

3.数据透视表

在对数据处理时，数据透视表也是一个十分有效的命令，可以动手试一试

如果看了这篇文章还是搞不定，可以直接给我留言

在评论区留言，告诉小编，你想学习哪些Excel技巧？

如果在一张表中使用公式时，比如用sum合计汇总，当我们填入数据时，合计栏中的数值也会跟着变化，如果删除所有数据，那么使用了sum公式的合计栏下单元格则会显示“0”，如图1所示：

图1

实际上表格并不会受到任何影响，只要在计算区域中输入数据，表格依然会自动进行合计汇总。可是如果在一张空白的表格中只显示几个0，看上去总不那么舒服，如何去除呢？请仔细看图2：

图2

在上面的表格中，我们在F2中输入的公式是=IF(SUM(B2:E2)<>0,SUM(B2:E2),"")，这个其实也是if函数的最简单直接的用法。

公式的含义是这样的：当B2：E2单元格的合计不为0的时候，在F2单元格中显示B2：E2单元格的合计结果，也就是sum（B2:E2），

如果B2：E2单元格的合计为0， 则F2单元格显示为空值（“ “），这样我们就可以去掉公式产生的0啦！

我们删除前面的数值时，合计栏的数值也没有了，而且带公式的单元格也不会显示‘’0‘’了，

是不是很神奇？希望对大家有帮助！

excel求和，有汉字怎么办？好烦人啊，有解决的办法吗？有！

很多用excel计算的朋友，会经常遇到这样的情况：

我们得到的excel数据

有人说这个不是很简单吗？我们在后面的单元格输入=B2*C2，往下一拉不就行了吗，来吧我们试试看：

excel出现的结果竟然是这样的：

简单的求和，excel出错了

那应该怎么办呢？假如我们想要的是，待定，未定，这些忽略，目前确定的数量是多少，我们在需要求和的单元格输入=SUMPRODUCT(B2:B6,C2:C6)，哇，竟然没出错，太神奇了！

excel在有汉字的情况下，完美的计算出了结果

这是什么意思呢？我们需要的结果是单价和数量乘积之和，而SUMPRODUCT是专门解决这个问题的函数，函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理，所以汉字就被excel视为0。

怎么样，很简单吧，你学会了吗？快去试试吧！非常感谢您观看文本，如果你有更好的方法适用这个情况，请您留言分享！分享好玩、有趣、实用的手机和电脑技巧，您的支持就是我的最大动力！

日常工作中，总会遇到一些奇葩的表格。

这些表格普遍具有以下特征：

1、使用大量的合并单元格

2、内容都挤在一个单元格内，名称和数量不分家

3、数量单位不统一，一包一盒一箱一把抓

……

俗话说，有病的时候不能放弃治疗。

接下来咱们就看看下图中的数据如何破：

B列中的数据就是名称数量不分家的典范了，这么多的单元格不用，所有的内容都挤在一个格子里。

话说妹纸啊，你这样做表，妹夫知道吗？

这样的数据如何计算每天的消费额呢？

首先复制Excel中的数据，粘贴到Word中：

按Ctrl +H调出替换对话框：

替换完成后的效果如下：

复制Word中的内容，粘贴到Excel中：

这样就计算出了每个单元格内的数值之和，也就是每天的消费总额。

替换时用到的[!0-9.]{1,} 是Word通配符

[!0-9.]的意思是除了0-9和小数点以外的字符

{1,} 是一个或一个以上的字符。

也就是将一个或一个以上的、除了0-9和小数点以外的字符替换为+（加号）

求和，是最常见的统计工作，但花样繁多的求和要求和条件，常常让不少人限入苦思冥想不得其解的困惑之中，今天运用EXCEL函数与公式，总结从初级到高级的10个求和技巧，与大家一起学习和交流。

初级篇

1、运用“=”、“+”来计算

这是最基础的求和方法，适合无规律或指定单元格求和。如下图：计算1、3、5月份的销售量。J2单元格：=E2+G2+I2，然后，向下复制

2、运用函数SUM求和

这是基础求和函数，适合连续区域求和，高级应用多为与其他函数结合形成SUM数组公式。说明一点，SUM函数的参数，可以是单元格区间，也可以是单个单元格。如下图：计算1、3、5月份的销售量。J2单元格：=SUM(E2,G2,I2)，然后，向下复制

中级篇

3、忽略文本跨列求和

如下图，计算全部销售人员的销售额。C10单元格：=SUM(A2:I7)

利用Excel中sum函数本身对文本类型数值自动忽略的规定，所以，可以用来直接对跨列的数值型数据求和。

4、单条件求和，运用求和函数：SUMIF。

如图，计算1月份冰箱的销售量。K2单元格：=SUMIF($D$2:$D$18,"冰箱",$E$2:$E$18)

SUMIF函数基本结构：SUMIF（条件区域，条件，求和区域）

5、多条件求和，运用求和函数：SUMIFS。

如图，计算销售一部1月份相机销售量。L2单元格：=SUMIFS($E$2:$E$18,$A$2:$A$18,"销售一部",D$2:D$18,"相机")

SUMIFS函数基本结构：SUMIF（求和区域，条件区域1，条件1，条件区域2，条件2...）

6、避开错误值求和。

如下图：计算1-5月份总销售量。C12单元格：=SUMIF(A2:E10,"<9.9E+307")

应用函数SUMIF设置条件完成，其中设置条件"<9.9E+307"，其含义是，9.9*10^307，即9.9乘10的307次方，9.9E+307是一个极大的数值，根据EXCEL的通过规则：数值<文本

7、通配符求和

如下图：计算1月份包含电脑的的总销量。G2单元格：=SUMIF(C2:C18,"*电脑",D2:D18)

应用函数SUMIF设置条件完成，通配符*代表任何字符或字符串，只要总字符不超过255个，设置条件"*电脑"，或“*电脑*”，在选定区域中查找包含“电脑”的所有商品，在计算区域求和。

8、快速累计求和

利用“$”对求和区域首个单元格进行绝对引用，马上就能对一列数值快速累计求和，如下图，C2单元格：=SUM($B$2:B2)，向下复制即可。

9、求和的快捷键："Alt + =",选中要求和的区域和拟计算结果的空白区域，一键即可在空白区域产生求和结果

高级篇

10、求和之王：SUMPRODUCT

SUMPRODUCT函数跟SUM函数的数组公式条件求和语法一样。

=SUMPRODUCT((条件区域=条件)*求和区域)

如上面案例，计算1月份冰箱的销售量。K2单元格：=SUMIF($D$2:$D$18,"冰箱",$E$2:$E$18)，也可以写成：=SUMPRODUCT((D2:D18="冰箱")*E2:E18)

如上面案例，计算销售一部1月份相机销售量。L2单元格：=SUMIFS($E$2:$E$18,$A$2:$A$18,"销售一部",D$2:D$18,"相机")，也可写成：=SUMPRODUCT((A2:A18="销售一部")*(D2:D18="相机")*E2:E18)

下面，举个复杂一点点的例子，昨天，世界杯开始，首场比赛，俄罗斯队5：0大胜沙特队，拟统计本次世界杯，各小组球队进、失球队总数。

以A组为例，第一场有真实比分，其余为模拟分。就可以用SUMPRODUCT计算，进球数：J2单元格：=SUMPRODUCT(($B$2:$B$7=$I3)*($C$2:$C$7))+SUMPRODUCT(($F$2:$F$7=$I3)*($E$2:$E$7))；失球数：K2单元格：=SUMPRODUCT(($B$2:$B$7=$I3)*($E$2:$E$7))+SUMPRODUCT(($F$2:$F$7=$I3)*($C$2:$C$7))

SUMPRODUCT与其他函数结合还可以解决你意想不到的求和需求，以后我们会专门进行分享。请关注后，期待好文。

每天学习一点点，每天进步一点点，积硅步，必能致千里，得大成。