Excel统计所有实例为COMPLETE的唯一ID总数（去重）

针对新版Excel（Excel 365/2021，支持动态数组） #

直接在C2单元格输入以下公式，回车即可：

=SUM(--(BYROW(UNIQUE(B:B),LAMBDA(x,COUNTIFS(B:B,x,A:A,"<>COMPLETE"))=0)))

公式说明： #

• UNIQUE(B:B)：自动提取B列所有不重复的ID
• BYROW+LAMBDA：逐个检查每个唯一ID，计算该ID对应的状态不是COMPLETE的记录数
• =0：如果非COMPLETE的记录数为0，说明这个ID的所有状态都是COMPLETE
• --(...)：把“是/否”的判断结果转成1或0
• SUM()：把所有符合条件的1相加，得到最终总数

针对旧版Excel（Excel 2019及更早，不支持动态数组） #

在C2单元格输入以下公式后，必须按Ctrl+Shift+Enter完成输入（不能直接回车）：

=SUM(IF(FREQUENCY(IF(COUNTIFS(B$2:B$21,B$2:B$21,A$2:A$21,"<>COMPLETE")=0,MATCH(B$2:B$21,B$2:B$21,0)),ROW(B$2:B$21)-ROW(B$2)+1)>0,1))

公式说明： #

• COUNTIFS(...)：先筛选出所有对应ID无任何非COMPLETE状态的行
• MATCH(...)：获取每个符合条件的ID第一次出现的位置
• FREQUENCY(...)：去重，确保每个ID只被统计一次
• SUM(IF(...))：统计符合条件的唯一ID数量

动态更新数据的小技巧 #

如果每天会新增ID数据，不想手动修改公式范围：

1. 选中A1到最后一行数据（包含表头）
2. 按Ctrl+T，勾选「我的表有标题」，将数据转成超级表
3. 之后新增的行数据会自动被公式纳入计算，无需调整公式里的单元格范围

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