SUMIF靠边站,这才是Excel中最牛的求和函数!
与 30万 粉丝一起学Excel

VIP学员的问题,左边是收款明细表,要统计今日、本月收款的套数和金额。套数需要根据房号去重复。

按条件统计金额很简单,按条件统计不重复的套数就有点难,卢子从先简单的说起。
1.今日收款金额
单条件求和,也就是SUMIF函数。
=SUMIF(A:A,H1,C:C)

语法:
=SUMIF(条件区域,条件,求和区域)
2.本月收款金额
因为都是具体的日期,需要提取月份,才可以按月份条件求和。SUMIF函数有一个缺陷,条件区域不能嵌套其他函数,除非用辅助列。
而SUMPRODUCT函数也可以用来条件求和,每个参数都支持嵌套其他函数,比较灵活。同样也有一个缺陷,不能直接引用整列。=SUMPRODUCT((MONTH(A2:A7)=MONTH(H1))*C2:C7)

语法:
=SUMPRODUCT((条件区域=条件)*求和区域)
也就是说,今日收款金额,也可以用SUMPRODUCT函数。=SUMPRODUCT((A2:A7=H1)*C2:C7)

3.今日收款套数
统计不重复数,有一个经典的套路。=SUMPRODUCT(1/COUNTIF(区域,区域))
按条件统计不重复数可以按下面这个新套路。=SUMPRODUCT((条件区域=条件)/COUNTIFS(区域1,区域1,区域2,区域2))
将新套路的每个参数换成实际区域,公式就出来了。2023/7/3总共有4行,而其中有一个房号重复,也就是不重复数为3,结果正确。=SUMPRODUCT((A2:A7=H1)/COUNTIFS(A2:A7,A2:A7,B2:B7,B2:B7))

对于一个新套路,最好多用几个案例验证结果。
比如统计每一天的不重复银行数,结果也是对的。区域要下拉,记得加美元锁定。
=SUMPRODUCT(($A$2:$A$10=E2)/(COUNTIFS($B$2:$B$10,$B$2:$B$10,$A$2:$A$10,$A$2:$A$10)))

4.本月收款套数
有了案例3,按月的只需用辅助列提取月份。=MONTH(A2)

剩下的就一样。
=SUMPRODUCT((E2:E7=MONTH(H1))/COUNTIFS(A2:A7,A2:A7,B2:B7,B2:B7))

当然,透视表也可以按条件统计不重复,而且更简单,不过布局有所差异,详见文章:按条件统计不重复的最简单办法!
SUMPRODUCT函数超级牛,只要你能想到的求和问题,基本都能解决。
链接:https://pan.baidu.com/s/1hRlj8-6iF9-ulOuPJOpVOQ?pwd=dv01
提取码:dv01
陪你学Excel,一生够不够?
一次报名成为VIP会员,所有课程永久免费学,永久答疑,仅需 1500 元,待你加入。
报名后加卢子微信chenxilu2019,发送报名截图邀请进群。
推荐:一直以为自己精通SUMPRODUCT函数,直到遇见这个神奇的套路!
上篇:Excel与Word间的复制粘贴,你会用吗?

请把「Excel不加班」推荐给你的朋友
手机如何去掉照片中多余的人物,教你快速消除方法,简单实用
站长网2023-07-28 12:16:230000在Microsoft Excel中如何快速合并表格
站长网2023-07-30 15:12:090000对账最头疼的名称不一样,试试这2条万能公式!
与30万粉丝一起学ExcelVIP学员的问题,左边是客户编码名称,右边是客户编码,要怎么核对两边的金额是否一样?这种规律性很强,操作起来并不难,跟卢子来看看。右边相当于是简称,左边相当于全称,核对的时候,加通配符*号就可以。0就代表找不到对应的。=SUMIF(A:A,"*"&E2&"*",B:B)左边的客户编码都是从第4位开始,提取11位,可以用MID提取。站长网2023-07-27 09:59:010000一分钟学会Excel动态图表
这样一个简单的动态图表,只需3步你就可以轻松拥有。当然,数据区域是基础,整理规范是基本要求。第1步,超级表按以下操作将数据区域变为超级表。框选数据区域【插入】→【表格】(注意看提示,也可以按快捷键【CTRLT】)第2步,切片器【设计表】→【插入切片器】注意,鼠标点选超级表区域的任意单元格时【设计表】选项卡才会出现。勾选“产品”切片器上依此排列着各项产品。站长网2023-07-29 14:27:070000SAP ERP系统PP模块控制BOM的变更是否需要ECN的三种解决方法
BOM简称物料清单,它是PC和MC部门编制生产计划,物料采购和生产计划的依据,也是财务成本核算的基础,它的重要性不言而喻。一个公司的生产管理模块(PP)应用的好坏很大程度上取决于它的BOM管理,不少公司就因BOM管理不规范,导致MRP都运行不起来。站长网2023-07-27 09:37:160000