Excel办公技巧·条件统计篇SUMPRODUCT和COUNTIFS

Excel条件统计,其实只要学会两个函数

每到月底,是不是总有这样的时刻:要对账、要汇总、要统计合格率,数据一大片,手工筛选看得眼睛发花,一个不小心还漏了行。

今天这篇,就把条件统计最核心的两个函数讲透:SUMPRODUCT(按条件求和)和COUNTIFS(按条件计数)。学会它们,对账汇总、合格率、及时率这些统计,基本都能一条公式解决。

01 先搞清一个前提:为什么要用条件统计

普通的SUM和COUNT是把所有数据一起算,但工作里几乎不会遇到"全算",更多是"只算符合条件的":只算北京门店的、只算中药品类的、只算验收合格的。

手工筛选再求和,慢、容易错、还不能重复用。SUMPRODUCT和COUNTIFS就是为"按条件算"准备的,一次写好,数据一更新,结果自动重算。

02 SUMPRODUCT:按多个条件求和

让满足多个条件的行参与计算,符合条件的数加起来,就是条件求和。

语法:=SUMPRODUCT((条件区域1=条件1)*(条件区域2=条件2)*求和区域)

示例:统计北京门店中药品类的销售额合计,公式这样写:

=SUMPRODUCT((A2:A100="北京")*(B2:B100="中药")*D2:D100)

拆开看:两个小括号里的判断会变成由1和0组成的数组,门店是北京、品类是中药的行,乘完还是原来的销售额,其余行全变成0,最后自动加总。这就是它做条件求和的原理,比SUMIFS更灵活,还能一口气处理多个条件。

它还能当计数器用:把最后的求和区域去掉,写成=SUMPRODUCT((A2:A100="北京")*(B2:B100="中药")),数出来的就是同时满足两个条件的行数。

03 COUNTIFS:按多个条件计数

数一数满足多个条件的记录有多少条。

语法:=COUNTIFS(条件区域1,条件1, 条件区域2, 条件2, ……)

示例1:统计抽检记录里合格的有多少批,公式:

=COUNTIFS(B2:B100,"合格")

示例2:直接算抽检合格率(合格批数除以总批数):

=COUNTIFS(B2:B100,"合格")/COUNTA(B2:B100)

把单元格格式设为百分比,合格率就出来了。到货及时率、订单满足率这类统计,都是一个套路:分子用COUNTIFS数满足条件的,分母用COUNTA或COUNT数总数。

04 四个最容易踩的坑

一:区域大小必须一致。SUMPRODUCT里每个区域的行数都要相同,否则结果错得莫名其妙,还很难发现。

二:条件的引号写法。SUMPRODUCT和COUNTIFS里的文本条件都要加英文双引号,例如"北京""合格";直接引用某个单元格(比如A2)就不用加。

三:乘号不能省。SUMPRODUCT里条件之间、条件和求和区域之间必须用*连接,漏掉一个*,结果就错了。

四:通配符只在COUNTIFS里好用。COUNTIFS支持*和?做模糊匹配,比如"*中药*"能统计所有包含中药的记录;SUMPRODUCT的等号判断不支持通配符,需要模糊匹配时优先用COUNTIFS。

05 组合实战:算到货及时率

场景:供应链到货统计,想知道北京仓按时到货的批次占比。

按时到货批次数:=COUNTIFS(A2:A100,"北京仓",C2:C100,"按时")

北京仓总批次数:=COUNTIF(A2:A100,"北京仓")

及时率=按时到货批次数÷总批次数,再把单元格设为百分比格式。

一个通用思路:统计类指标几乎都能拆成"分子用COUNTIFS,分母用COUNTIF或COUNTA",拆完套公式,报表几分钟就能出来。

两个函数能覆盖大多数条件统计场景:要加总,用SUMPRODUCT;要数个数,用COUNTIFS。建议先拿一份真实表格练一次,把区域、条件、乘号和引号这几个要点记住,以后对账统计基本不用再手工筛选了。觉得有用,可以先收藏,下次做报表直接照着套。