一、案例

如下图所示,B6:D15为各销售部门业务员销售额数据。要求对D列“销售额”数据进行筛选时,分别统计销售1部、销售2部、销售3部在筛选条件下的销售额合计。

怎样把筛选的数据求和(如何对筛选后的数据条件求和)(1)

例如,当未对销售额筛选时,C2:D4统计的是各销售部所有业务员销售额合计。

怎样把筛选的数据求和(如何对筛选后的数据条件求和)(2)

当筛选销售额大于500的数据时,C2:D4统计的是各销售部销售额超过500的业务员销售额合计。

怎样把筛选的数据求和(如何对筛选后的数据条件求和)(3)

二、解决方法

在C2单元格输入公式

=SUMPRODUCT(($B$7:$B$15=B2)*($D$7:$D$15)*SUBTOTAL(102,OFFSET($D$7,ROW($D$7:$D$15)-MIN(ROW($D$7:$D$15)),0)))

拖动填充柄向下复制公式。

怎样把筛选的数据求和(如何对筛选后的数据条件求和)(4)

公式解析:

(1)($B$7:$B$15=B2)用于判断B7:B15中的部门数据是否等于“销售1部”,返回的结果为

{FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}。返回的True和False逻辑值会在乘法运算时转为1和0。

(2)($B$7:$B$15=B2)*($D$7:$D$15)用于将不同部门的销售额数据区分开。当B7:B15中的部门是“销售1部”时,返回D7:D15中对应的销售额;反之返回0。

(3)SUBTOTAL函数用于判断D7:D15中的销售额数据是否在筛选范围之内。如果D7:D15的数据被筛选掉,SUBTOTAL函数返回0;如果D7:D17的数据未被筛选掉,SUBTOTAL函数返回1。

(4)SUBTOTAL的语法为SUBTOTAL(function_num,ref1,[ref2],...)。本例中,参数function_num为102,统计区域中包含数字的单元格的个数;参数ref1为OFFSET函数返回的引用。

(5)ROW($D$7:$D$15)-MIN(ROW($D$7:$D$15))

返回数组{0;1;2;3;4;5;6;7;8}。

(6)OFFSET($D$7,ROW($D$7:$D$15)-MIN(ROW($D$7:$D$15)),0)即

OFFSET($D$7,{0;1;2;3;4;5;6;7;8},0)。指以D7单元格为起点,分别向下移动0、1、2…8行。

(7)SUBTOTAL函数判断OFFSET返回的每个引用是否被筛选出来。当筛选销售额大于500的数据时,SUBTOTAL返回的结果为{0;0;1;0;0;1;1;0;1},“0”表示该行的数据被筛选掉,“1”表示该行数据未被筛选掉。

,