Excel的SUMIF/SUMIFS/COUNTIF/COUNTIFS函数可以用于条件求和/条件计数,但是当条件区域为合并单元格时,这些函数的结果是错误的。
本文和大家分享当条件区域是合并单元格时,如何进行条件求和。
在本文结尾附有条件计数的小案例。当你理解如何在合并单元格区域进行条件求和时,也能轻松解决条件计数问题。
一、案例
如下图所示,A1:B10为产品销量表。要求计算D2单元格指定产品的总销量。
二、计算步骤
在E2单元格输入公式
=SUM((LOOKUP(ROW($2:$10),IF($A$2:$A$10<>"",ROW($2:$10)),$A$2:$A$10)=D2)*$B$2:$B$10)
按Ctrl+Shift+Enter结束公式输入。
公式解析:
(1)对A列取消合并,结果如下图所示。
可见只有A2、A5、A7单元格有数据,A3:A4、A6、A8:A10均为空值。这也是使用SUMIFS函数条件求和结果错误的原因。
本例使用LOOKUP+IF函数将A2:A10中空值的单元格补齐相应的产品数据。
(2)IF($A$2:$A$10<>"",ROW($2:$10))返回A2:A10非空单元格的行号与FALSE组成的数组,
即{2;FALSE;FALSE;5;FALSE;7;FALSE;FALSE;FALSE}
(3)
LOOKUP(ROW($2:$10),IF($A$2:$A$10<>"",ROW($2:$10)),$A$2:$A$10)
即
LOOKUP(ROW($2:$10), {2;FALSE;FALSE;5;FALSE;7;FALSE;FALSE;FALSE} ,$A$2:$A$10)
该公式表示LOOKUP函数在{2;FALSE;FALSE;5;FALSE;7;FALSE;FALSE;FALSE}中查找{2;3;4;5;6;7;8;9;10},并返回与查找到的值同一行的A2:A10的值。
例如查找值为“2”,则返回A2单元格的值“产品A;
查找值为“3”,在{2;FALSE;FALSE;5;FALSE;7;FALSE;FALSE;FALSE}中没有3,LOOKUP函数查找小于“3”的最大值,也就是“2”,返回A2单元格的值“产品A”。
以此类推,LOOKUP函数最终返回的结果为{"产品A";"产品A";"产品A";"产品B";"产品B";"产品C";"产品C";"产品C";"产品C"}。这样就可以将解析(1)中提到的A2:A10中的空值补齐。
(4)
LOOKUP(ROW($2:$10),IF($A$2:$A$10<>"",ROW($2:$10)),$A$2:$A$10)=D2,将LOOKUP函数返回的{"产品A";"产品A";"产品A";"产品B";"产品B";"产品C";"产品C";"产品C";"产品C"}与D2单元格指定的产品名称比较,返回的结果为一组True和False值,即{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}
(5)
SUM((LOOKUP(ROW($2:$10),IF($A$2:$A$10<>"",ROW($2:$10)),$A$2:$A$10)=D2)*$B$2:$B$10),将满足条件的销量求和。
扩展(合并单元格条件计数):
A1:B10为公司各部门人员名单。你知道如何用公式统计“销售部”总人数吗?
可以在E2单元格输入公式:
=SUM(--(LOOKUP(ROW($2:$10),IF($A$2:$A$10<>"",ROW($2:$10)),$A$2:$A$10)=D2))
按Ctrl+Shift+Enter键结束公式输入。
END
联系客服