多条件统计-函数

 前言

前面介绍了用数据透视表和透视图进行多条件统计，但是当源数据增加时，需要重新调整数据透视表选择区域，并手动刷新。今天我们返回来讲一下大家最擅长的公式解决方案。

 公式

 对于多条件统计和求和，Sumproduct函数绝对独领风骚，藐视群雄。今天我们就看一下它的威力。

在F2单元格输入公式：

=SUMPRODUCT((--TEXT($B$2:$B$8,"yyyymm")=F$1)*($C$2:$C$8=$E2))

下拉，右拉，ok。

 解释

TEXT($B$2:$B$8,"yyyymm")

将B列的日期格式变成文本格式YYYYMM

--TEXT($B$2:$B$8,"yyyymm")

将文本格式强制转换成数字格式

--TEXT($B$2:$B$8,"yyyymm")=F$1

判断年月是否和F1相等，相等返回TRUE,否则返回FALSE

注意：此例中F1：201801为数字格式，不是日期格式2018年1月，如果F1为日期2018-01-01的自定义格式201801）可以写成

TEXT($B$2:$B$8,"yyyymm")= TEXT(F$1,"yyyymm")

或

(Year($B$2:$B$8)=Year(F$1))* (Month($B$2:$B$8)=month(F$1))

确保年月相同，特别是存在跨年的情况，单独的判断月是不可以的。

$C$2:$C$8=$E2

判断C列是否等于E2

SUMPRODUCT((--TEXT($B$2:$B$8,"yyyymm")=F$1)*($C$2:$C$8=$E2))

两个条件用乘号连接，将强制将逻辑值转化成1或0，从而实现多条件计数。

用sumproduct函数是不是很简单呢？

 
扩展

为了增加可扩展性，可以把计算的区域扩大到1000行或更多，这样直接在数据区域增加数据就可以了，只要不超过这个最大行就就不会影响公式结果。

SUMPRODUCT((--TEXT($B$2:$B$1000,"yyyymm")=F$1)*($C$2:$C$1000=$E2))

当然也可以让公式自动判断统计区域：

=SUMPRODUCT((--TEXT(OFFSET($B$1,1,0,COUNTA($A:$A)-1,1),"yyyymm")=F$1)*(OFFSET($C$1,1,0,COUNTA($A:$A)-1,1)=$E2))

这个稍微有点复杂，用Offset和Counta两个函数，动态定义了引用区域，当在A列继续输入数据时，统计范围会同步调整。

 知识点

Text

Offset

Counta

Sumproduct

Year、Month

--负负得正

True*True=1

True*False=0

False*False=0

END

欢迎入群讨论！(QQ群)