Excel按A列状态条件统计B列唯一值的实现方法

需求说明 #

需要基于A列的状态统计B列的唯一地址数量，结果分别填入E2和E3单元格：

• 完成数（E2）：只要对应地址的任意一条A列状态为Cancelled或Installed，即计入统计
• 待办数（E3）：对应地址的所有A列状态均为Pending，才计入统计

公式方案 #

适用Excel 365/2021及以上版本 #

• E2单元格输入：=SUMPRODUCT(--(COUNTIFS(B:B,UNIQUE(FILTER(B:B,B:B<>"")),A:A,"<>Pending")>0))
• E3单元格输入：=SUMPRODUCT(--(COUNTIFS(B:B,UNIQUE(FILTER(B:B,B:B<>"")),A:A,"<>Pending")=0))
  上述公式已经自动排除B列空值干扰，直接回车即可生成结果，无需下拉填充。

适用低版本Excel（无动态数组函数） #

先新增辅助列标记地址属性：

1. 任意空白列（例如C列）C2单元格输入公式，下拉填充到所有数据行：=IF(COUNTIFS(B:B,B2,A:A,"<>Pending")>0,"完成","待办")
2. E2单元格输入：=SUMPRODUCT(1/COUNTIFS(B:B,B:B,C:C,"完成",B:B,"<>"))
3. E3单元格输入：=SUMPRODUCT(1/COUNTIFS(B:B,B:B,C:C,"待办",B:B,"<>"))
   输入公式后按Ctrl+Shift+Enter三键结束数组运算即可。

内容的提问来源于stack exchange，提问作者Morgander