精华内容
下载资源
问答
  • 编按:按条件求和,工作中很常见。如果是根据条件求单列数据之和,SUMIF函数即可解决,但如果是求多列数据呢?我们这里分享12种方法,各有各的特色。学习更多技巧,请收藏关注部落窝教育excel图文教程。先来看一下...

    685ed702e22837ff3fcd65962b5813c7.png

    编按:按条件求和,工作中很常见。如果是根据条件求单列数据之和,SUMIF函数即可解决,但如果是求多列数据呢?我们这里分享12种方法,各有各的特色。学习更多技巧,请收藏关注部落窝教育excel图文教程。


    先来看一下什么是按条件求多列数据之和。

    类似下图这样的数据,需要根据G列的产品名称在H列汇总数据。条件区域在B列,而要求和的数据在C、D、E三列中。这种求和就是按条件求多列数据之和,简称多列条件求和。

    aea14b2b12383646e03d078563c7814f.png

    这类条件求和,在实际工作中经常会遇到,但直接用一个SUMIF函数或者透视表是无法完成的。

    今天给大家分享解决这个问题的12个套路公式(有没有被惊到?),当然你能掌握其中的两三种就够用了(请允许我像孔乙己那样炫耀一回)。

    公式1:=SUMIF(B:B,G2,C:C)+SUMIF(B:B,G2,D:D)+SUMIF(B:B,G2,E:E)

    9b2090f459cf7e980348d5e1bb323fd5.png

    刚才说过无法直接用一个sumif函数求和,因为sumif要求条件区域和求和区域大小相同,而本例显然不满足这个要求。

    用三个sumif分别求和后再相加,这不难理解,但是如果要求和的列更多的话,还是有点麻烦。

    公式2:=SUM(IF(B$2:B$16=G2,C$2:E$16))

    894c012454b77214e86eb2c989f4c378.png

    这是一个数组公式,需要按住Ctrl、shift和回车键完成输入。

    数组有自扩展性,利用这个特性就可以将一列条件与三列数据进行判断。满足条件的时候为对应数字,不满足条件时得到FALSE,这是if函数省略第三参数以及第三参数前逗号的用法。

    在这个公式中,用if做条件判断得到需要求和的数字,再用sum实现最终的求和结果。

    公式3:=SUM((B$2:B$16=G2)*C$2:E$16)

    e8084eee613aa396cee69a042766503f.png

    这个公式是比较常用的一种套路,与公式2的区别在于少了用if函数进行判断,它直接利用了逻辑值参与计算。公式同样需要三键输入。

    如果不习惯三键的话,SUM数组公式可以用SUMPRODUCT函数取代。关于SUMPRODUCT函数的用法可以查看《加了*的 SUMPRODUCT函数无所不能》。

    公式为:=SUMPRODUCT((B$2:B$16=G2)*C$2:E$16),两个公式原理完全一致,可以视为同样的公式。

    公式4:=SUMPRODUCT((B$2:B$16=G2)*(C$2:C$16+D$2:D$16+E$2:E$16))

    a7064677369b2d8dd9b97e15316b72f4.png

    这可以视为公式3的另一种思路,当求和区域是连续的多列时,两个公式都可以用;如果要求和的多列是不连续的,例如只求第1周和第3周的和,则只适合用公式4。

    以上四个公式都属于比较基础、常用的套路。

    下面要分享的公式,会涉及一些稍有难度或者难以理解的函数。如果你有一定的基础,可以结合公式自己去研究一下;如果感到难以理解的话,也可以先收起来,作为日后学习的一个方向。

    公式5:=SUMPRODUCT((B$2:B$16=G2)*MMULT(C$2:E$16,{1;1;1}))

    ffe08c0190caaeff8c30bee3d0832e6a.png

    SUMPRODUCT和MMULT函数联手,感到蒙圈了没有?

    公式6:=SUM(MMULT((B$2:B$16=G2)*C$2:E$16,{1;1;1}))

    e46e29a7e61a47d8327407ea4d3ee168.png

    注意哦,这个公式可不是简单的把SUMPRODUCT换成SUM了。学习更多技巧,请收藏关注部落窝教育excel图文教程。

    要看懂这两个公式,必须对MMULT函数有所了解。如果对这个函数还比较陌生的话,咱们换一个大家稍微熟悉点的OFFSET函数也可以。对OFFSET不熟悉的可以查看《Excel进阶之路必学函数:动态统计之王——OFFSET(上篇)》。

    公式7:=SUM(SUMIF(B:B,G2,OFFSET(B:B,,{1,2,3})))

    5a06bfc09b1a66ba1c33100e3135c32d.png

    这个公式其实是对公式1的优化,利用OFFSET得到了三个一列的求和区域,相当于用一个SUMIF和OFFSET实现了三个SUMIF的工作。公式的优势在于当求和列增加的时候,只需要在OFFSET里增加偏移数即可。

    通常能用OFFSET构造的多区域数据,INDIRECT也可以搞。

    公式8:=SUM(SUMIF(B:B,G2,INDIRECT("c"&{3,4,5},)))

    775a8e4504ee195e3f6f8b5d1755263b.png

    INDIRECT函数比较牛的地方是有两种引用方式,也就是RC模式和A1模式,函数的第二参数就是确定使用何种引用方式的。

    公式9:=SUM(SUMIF(B:B,G2,INDIRECT({"c","d","e"}&1)))

    a2c95e2b9bfdaa467445facc77c2f842.png

    注意仔细区分这两个公式中INDIRECT里的区别。

    实际上,7、8、9这三个公式的思路差不多,都是用函数构造多个单列区域,为SUMIF服务,区别只是OFFSET与INDIRECT,以及INDIRECT的两种引用形式。

    公式10:=SUM(DSUM(A$1:E$16,{3,4,5},G$1:G2))-SUM(H$1:H1)

    e0a51cce7298dad19ced0e2346e0f409.png

    这个公式的关键是DSUM函数。DSUM是一个数据库类的求和函数,可以实现条件求和,有兴趣的朋友可以自己了解一下这个函数,看看教程《DSUM,最简单的条件求和函数!你知道不?》。

    公式11:=SUMPRODUCT(COUNTIF(G2,B$2:B$16)*C$2:E$16)

    19ed94147be9e513b5f75ef809c670fb.png

    SUMPRODUCT和COUNTIF都是比较常用的函数。这个公式中,COUNTIF充当了条件判断的角色,你能看明白其中的门道吗?

    公式12:=MMULT(MMULT(N(G2:G6=TRANSPOSE(B2:B16)),C2:E16),{1;1;1})

    6fa4b474972e384dfd7741068e5beba9.png

    最后这个公式无疑是很有分量的,不然不足以压轴。

    特别要说明的一点是这个公式要选定公式区域,然后按照数组公式的输入方式完成,而不是先有数组公式再下拉的那种用法。

    12个公式有很简单的,也有比较难的,有你能看懂能使用的,也有你暂时还无法理解的。但不管怎样,相信你都能通过今天的内容有一些新的收获。学习更多技巧,请收藏关注

    部落窝教育www.itblw.com


    ****部落窝教育-excel多列数据条件求和****

    原创:老菜鸟/部落窝教育(未经同意,请勿转载)

    更多教程:

    部落窝教育www.itblw.com

    微信公众号:exceljiaocheng


    做Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

    相关推荐:

    DSUM,最简单的条件求和函数!你知道不?

    无往而不利的SUMIF面对这种条件求和竟然傻眼了!

    加了*的 SUMPRODUCT函数无所不能

    Excel进阶之路必学函数:动态统计之王——OFFSET(上篇)

    Excel进阶之路必学函数:动态统计之王——OFFSET(下篇)

    展开全文
  • 使用SUMIF()公式的单条件求和: 如要统计C列中的数据,要求统计条件是B列中数据为"条件一"。并将结果放在C6单元格中,我们只要在C6单元格中输入公式“=SUMIF(B2:B5,"条件一",C2:C5)”即完成这一统计。 ...

    经常和Execl打交道的人肯定觉得求和公式是大家时常用到的。Excel里有哪几路求和公式呢?他们的使用方式又是怎样?我为大家汇总一下。

     

    1. 使用SUMIF()公式的单条件求和:

      如要统计C列中的数据,要求统计条件是B列中数据为"条件一"。并将结果放在C6单元格中,我们只要在C6单元格中输入公式“=SUMIF(B2:B5,"条件一",C2:C5)”即完成这一统计。

    2.  

      SUM()函数+IF()函数嵌套的方式双条件求和:

      如统计生产一班生产的质量为“合格”产品的总数,并将结果放在E6单元格中,我们用“条件求和”功能来实现:

      ①选“工具→向导→条件求和”命令,在弹出的对话框中,按右下带“―”号的按钮,用鼠标选定D1:I5区域,并按窗口右边带红色箭头的按钮(恢复对话框状态)。

      ②按“下一步”,在弹出的对话框中,按“求和列”右边的下拉按钮选中“生产量”项,再分别按“条件列、运算符、比较值”右边的下拉按钮,依次选中“生产班组”、“=”(默认)、“生产一班”选项,最后按“添加条件”按钮。重复前述操作,将“条件列、运算符、比较值”设置为“质量”、“=”、“合格”,并按“添加条件”按钮。

      ③两次点击“下一步”,在弹出的对话框中,按右下带“―”号的按钮,用鼠标选定E6单元格,并按窗口右边带红色箭头的按钮。

      ④按“完成”按钮,此时符合条件的汇总结果将自动、准确地显示在E6单元格中。

      其实上述四步是可以用一段公式来完成的,因为公式中含有数组公式,在E6单元格中直接输入公式:=SUM(IF(D2:D5="生产一班",IF(I2:I5="合格",E2:E5))),然后再同时按住Ctrl+Shift+Enter键,才能让输入的公式生效。

      上面的IF公式也可以改一改,SUM(IF((D2:D5="生产一班")*(I2:I5="合格"),E2:E5)),也是一样的,你可以灵活应用,不过注意,IF的嵌套最多7层。

      除了上面两个我常用的方法外,另外我发现网络上有一个利用数组乘积函数的,这是在百度上发现的,我推荐一下:

    3.  

      SUMPRODUCT()函数方式:

      表格为:

      A    B   C   D

      1 姓名  班 级  性别  余额

      2 张三  三年五  女   98

      3 李四  三年五  男   105

      4 王五  三年五  女   33

      5 李六  三年五  女   46

      现在求求出三年五班女生的总余额。

      公式:=SUMPRODUCT((B2:B5="三年五")*(C2:C5="女")*(D2:D5))

      解释:SUMPRODUCT在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。

      语法 SUMPRODUCT(array1,array2,array3, ...) Array1, array2, array3, ... 为 2 到 30 个数组,其相应元素需要进行相乘并求和。

      在这里((B2:B5="三年五")*(C2:C5="女")*(D2:D5))为一个数组,其中(B2:B5="三年五")*(C2:C5="女")为确定满足准备求和条件的单元格所在的行,*(D2:D5)则是满足求和条件的单元格的范围。

      第三个函数不常用,我对它也不是很了解,为尊重作者原著,我只有把他的内容贴出来,请见谅。

      其实上面的第二个方式(sum函数嵌套if函数)和第三个方式(sumproduct函数)不但可以实现双条件求和,也可以实现三条件求和甚至多条件求和的。只不过函数稍微变更一下的。

    4. 4

      SUMIFS函数

      该函数在EXCEL2007中被广泛使用,使用起来更加方便,例如第三个方式中的例子,我们就可以输入公式:“=SUMIFS(D2:D5,B2:B5,"三年五",C2:C5,"女")”,是不是更轻松?

    转载于:https://www.cnblogs.com/xuan52rock/p/4387964.html

    展开全文
  • Excel 求和SUM 求和函数语法SUMIF 条件求和函数语法SUMIFS 多条件求和函数语法SUBTOTAL 可见区域求和语法参考资料 SUM 求和函数 对指定区域的所有内容进行求和。 语法 SUM(number1, [number2], ...) 参数 必填 ...

    SUM 求和函数

    指定区域所有内容进行求和。

    语法

    SUM(number1, [number2], ...)

    参数 必填 说明
    numberN 支持:数字、单元格引用、单元格范围引用。

    SUMIF 条件求和函数

    指定区域满足条件的内容进行求和。

    语法

    SUMIF(range, criteria, [sum_range]

    参数 必填 说明
    Range 进行条件判断的单元格区域。
    Criteria 条件。除数字个引用外其他值要加引号。
    例如,条件可以表示为 9527、A3、">66"、“6?”、“fuc*”、"*~?" 、 “南瓜”
    sum_range 求和内容所在的区域。如果省略,直接对条件区域进行求和。必须包含数字或对数字人引用。空白和文本值将被忽略。

    SUMIFS 多条件求和函数

    满足多个条件的内容进行求和

    语法

    SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
    例子:=SUMIFS(A3:A10,B3:B:10,B13,C3:C10,A13) 对单元格 A3 到 A10 中的某些值进行求和,其中这些值所在行中 B 列的值等于 B13,C 列的值等于 A13。

    参数 必填 说明
    sum_range 求和区域。对此区域内容进行求和
    criteria_range1 条件区域1
    criteria1 条件1

    SUBTOTAL 可见区域求和

    对指定区域分类汇总。第一个参数指定分类汇总的函数编号。(109表示 sum 忽略不可见区域)

    语法

    SUBTOTAL(function_num, ref1, [ref2], ...)

    参数 必填 说明
    Function_num 用于分类汇总的函数,详情见下表。
    Ref1 要对其进行分类汇总计算的第一个命名区域或引用。
    Ref2 要对其进行分类汇总计算的第 2 个至第 254 个命名区域或引用。
    Function_num (包含隐藏值) Function_num (忽略隐藏值)                                                                函数                                                               
    1 101 AVERAGE 求数据平均值
    2 102 COUNT 统计参数的个数
    3 103 COUNTA 返回参数列表中非空单元格的个数
    4 104 MAX
    5 105 MIN
    6 106 PRODUCT 将所有以参数形式给出的数字相乘,并返回乘积值
    7 107 STDEV 估算样本的标准偏差。标准偏差反映相对于平均值 (mean) 的离散程度
    8 108 STDEVP 返回以参数形式给出的整个样本总体的标准偏差。标准偏差反映相对于平均值 (mean) 的离散程度
    9 109 SUM
    10 110 VAR 计算基于给定样本的方差
    11 111 VARP 计算基于整个样本总体的方差

    温馨提示

    如果是想按条件记数应该用:COUNTIF

    参考资料

    WPS帮助:SUM函数
    WPS帮助:SUMIF函数
    WPS帮助:SUMIFS函数
    WPS帮助:SUBTOTAL函数

    Office帮助:SUM函数
    Office帮助:SUMIF函数
    Office帮助:SUMIFS函数
    Office帮助:SUBTOTAL函数

    展开全文
  • 文章目录sumifs函数单条件求和条件求和模糊条件求和 sumifs函数 单条件求和 =sumifs(求和区域,条件区域,条件1) 多条件求和 =sumifs(求和区域,条件区域1,条件1,条件区域2,条件2) 模糊条件求和 ...

    sumifs函数

    在这里插入图片描述

    单条件求和

    =sumifs(求和区域,条件区域,条件1)
    在这里插入图片描述

    多条件求和

    =sumifs(求和区域,条件区域1,条件1,条件区域2,条件2)
    在这里插入图片描述

    模糊条件求和

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    展开全文
  • ”别担心,今天老菜鸟将给大家分享三种用公式直接忽略错误值求和的方法,赶紧来看看吧!***************按照对应的订单号引用已有的收货金额,这种问题相信很多朋友都会处理,用VLOOKUP...
  • 其实,求和不仅仅只有命令或sum函数可以完成,还可以与快捷键、条件求和、相对绝对引用结合使用。一、求和函数SUM01、快捷键在表格内调取sum函数是我们刚刚开始接触Execl函数时就会使用的操作,在这里不在重复,首先...
  • 需要对行和列快速的进行求和汇总,不需要手动的去输入SUM公式,只需要全选表格区域,按ALT+=快捷键,结果就全部出来了。1、行列快速求和需要对行和列快速的进行求和汇总,不需要手动的去输入SUM公式,只需要全选表格...
  • 02这是多条件求和,我们可以用到SUMIFS函数来实现。在D2单元格输入公式:=SUMIFS(C2:C11,B2:B11,"女",C2:C11,">80")03按下回车键,即得到结果301。从公式=SUMIFS(C2:C11,B2:B11,"女",C2:C...
  • excelCSS布局HTML小编今天和大家分享和公式excel 的sumCSS布局HTML小编今天和大家分享和公式在excel中大家都知道,第5列是E列,CSS布局HTML小编今天和大家分享和公式为=SUM(E:E),那么我要CSS布局HTML小编今天和大家...
  • 条件求和sumif

    2021-01-06 18:15:34
    场景:想要对一个区域内,符合条件的数据进行求和 1、函数结构 SUMIF(range,criteria,sum_range) range为条件所在的区域; criteria为求和条件; sum_range是求和区域。 2、示例说明 ①如下图,我们想要统计每组...
  • 一、excel表按条件求和 运用公式sumif求和,应用于后勤账目合计, 输入“=sumif” , 例如:=sumif(条件区域,选择条件,求和区域) 二、excel表按多条件求和 运用公式sumifs, 应用于考勤报表,输入“sumif”,...
  • Excel中怎么用公式计算合计在EXCEL表中可以用SUM函数或“ ”符号进行求和运如=A1 A2 A3 B1 B2 B3用SUM函数可以写=SUM(A1:B3)操作方法如下:1、首先点击excl2、然后输入相关信息3、然后在F2输入=sumif(c2:c7,“>...
  • 这个题今天终于解决了,谢谢eoj上shwei大佬的代码呀! 本题链接如下: ... ...判断y=x与y=n-1-x两条直线在矩形区域内的整点个数: ...=x2计算每个点的y1,y2值判断在不在...思路二:判断矩形区域与直线的交点,每条直线...
  • SUMIF函数的语法和功能 Excel中的SUMIF函数的主要作用是按给定条件对指定单元格求和,即根据指定条件对若干单元格、区域或引用求和。 函数语法:SUMIF(range,criteria,[sum_range]) 其中range是必需项,表示条件...
  • 1、IF要求:如果部门为生产、岗位为主操 有高温补助。...2、SUMIF多条件求和要求:统计E2和E3单元格中两个部门的岗位补助总额公式:=SUMPRODUCT(SUMIF(B2:B9,E2:E3,C2:C9))SUMIF函数求和条件使用E2:E3,分别得到两个...
  • sumif 根据条件求和

    2013-09-02 16:00:26
    使用 SUMIF 函数可以对区域中符合指定条件的值求和。例如,假设在含有数字的某一列中,需要对大于 5 的数值求和。请使用以下公式: =SUMIF(B2:B25,">5") 在本例中,应用条件的值即要求和的值。如果需要...
  • Excel 如何按条件计数和按条件求和(如按月求和) 1、使用SUMPRODUCT进行多条件计数语法:=SUMPRODUCT((条件1)*(条件2)*(条件3)* …(条件n))作用:统计同时满足条件1、条件2到条件n的记录的个数。实例:...
  • 条件求和向导法

    2009-06-26 13:47:00
    条件求和向导法 1、打开登记表,执行“工具加载宏”命令,打开“加载宏”对话框(如图5),选中“条件求和向导”选项,按下“确定”按钮,然后按提示操作加载“条件求和向导”功能。 注意:第一次使用这个功能时,...
  • EXCEL-SUMIFS多条件求和(筛选效果)

    千次阅读 2017-03-10 08:48:03
    EXCEL-SUMIFS多条件求和(筛选效果)
  • 在 Excel 中对多行多列进行条件求和

    万次阅读 2018-12-17 03:21:08
    在 Excel 中对多行多列进行条件求和问题由来源数据格式我的解决过程用 SUMPRODUCT 函数的失败过程分析错误解决问题用 SUMPRODUCT 解决问题我在 CSDN 的第一篇博客 问题由来 前几天,一名网友在微信群里求助,说有一...
  • excel中多条件求和 在Excel中求和的7种方法 (7 Ways to Sum in Excel)What was the first function that you learned in Excel – if you can remember that far back? 如果您还记得很久,您在Excel中学习的第一个...
  • excel中sumifs函数是Excel2007以后版本新增的多条件求和函数。 sumifs函数的语法是:SUMIFS(求和区域, 条件区域1,条件1, [条件区域2,条件2], ...) 说明:[]以内的条件区域2、条件2为可选参数。最多允许 127 个区域...
  • 在EXCEL中我们经常会用到很多计算,而求和是使用频率最高的计算了,今天我们就讲解一下EXCEL中3个最好用的求和函数,SUM函数、SUMIF函数、SUMIFS函数,我们看下面几个案例。 ...2、按条件求和...
  • EXCEL:SUMIFS多条件求和2(筛选效果),含字符串转数组用法
  • 日常工作中,我们经常要用到多条件求和,方法有多种,第一类:使用基本功能来实现。主要有:筛选、分类汇总、数据透视表、多条件求和向导;第二类:使用公式来实现方法。主要有:使用SUM函数编写的数组公式、联用...
  • 统计学生的成绩是老师必不可少的工作之一,每个班级的... ①选中M2单元格,单击“插入函数”按钮,在常用公式列表中选择“多条件求和”,参数中待求和区域为班级,条件1为班级等于1,条件2为语文不小于100分,然后...
  • sumif函数在办公中按照条件求和用的比较多,excel按条件求和,也是财务、人事、物控、仓库等办公人员用的较多的函数,sumif函数按条件求和的使用,大大的提升了办公效率,下面我们就来了解sumif函数SUMIF函数用于对...
  • 条件求和,大家并陌生,但是你真的会吗?除了用Sumif函数外,还可以怎么做?一、单条件求和Sumif功能:单条件求和。语法结构:=Sumif(条件范围,条件,[求和范围])。目的:按“学历”计算总“月薪”。方法:在目标...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 8,251
精华内容 3,300
关键字:

区域条件求和公式