非VBA实现：用Excel公式按分组统计‘New’记录数量

先明确表格结构（可根据实际调整范围） #

• TABLE1：A列=记录编号，B列=位置，C列=状态（数据范围示例：A2:C100）
• TABLE2：E列=位置，F列=分组（数据范围示例：E2:F100）
• TABLE3：G列=待统计的分组名称（从G2开始），H列放统计结果

方案1：适配Excel 365/2021（动态数组+XLOOKUP） #

在TABLE3的H2单元格输入以下公式，下拉填充即可：

=SUMPRODUCT((XLOOKUP(TABLE1!$B$2:$B$100,TABLE2!$E$2:$E$100,TABLE2!$F$2:$F$100)=G2)*(TABLE1!$C$2:$C$100="New"))

公式拆解 #

• XLOOKUP(TABLE1!$B$2:$B$100,TABLE2!$E$2:$E$100,TABLE2!$F$2:$F$100)：把TABLE1里每个位置对应到TABLE2的分组
• (XLOOKUP(...) = G2)：判断该记录的分组是否等于当前TABLE3要统计的分组
• (TABLE1!$C$2:$C$100="New")：筛选状态为New的记录
• SUMPRODUCT：将所有同时满足两个条件的记录计数求和

方案2：适配所有Excel版本（兼容旧版） #

在TABLE3的H2单元格输入以下公式：

=SUMPRODUCT((TABLE2!$F$2:$F$100=G2)*(ISNUMBER(MATCH(TABLE1!$B$2:$B$100,TABLE2!$E$2:$E$100,0)))*(TABLE1!$C$2:$C$100="New"))

公式拆解 #

• (TABLE2!$F$2:$F$100=G2)：定位TABLE2中当前分组对应的所有位置
• ISNUMBER(MATCH(TABLE1!$B$2:$B$100,TABLE2!$E$2:$E$100,0))：筛选TABLE1中存在于TABLE2的有效位置记录
• (TABLE1!$C$2:$C$100="New")：筛选状态为New的记录
• SUMPRODUCT：对同时满足三个条件的记录进行计数求和

如果是Excel 2019及更早版本，也可以用数组公式（输入后按Ctrl+Shift+Enter确认）：

=SUM(IF((TABLE2!$F$2:$F$100=G2)*(TABLE1!$B$2:$B$100=TABLE2!$E$2:$E$100)*(TABLE1!$C$2:$C$100="New"),1,0))

内容的提问来源于stack exchange，提问作者Shawn V. Wilson