Excel统计可见单元格中值为Defined的数量：如何引用列末行？

替换问号的解决方案 #

把公式中所有B2:B?替换为B2:INDEX(B:B,MATCH("*",B:B,-1))，完整公式如下：

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:INDEX(B:B,MATCH("*",B:B,-1)),ROW(B2:INDEX(B:B,MATCH("*",B:B,-1)))-ROW(B2),,1)),--(B2:INDEX(B:B,MATCH("*",B:B,-1))="Defined"))

关键部分说明 #

• MATCH("*",B:B,-1)：精准定位B列最后一个非空单元格的行号（不受单元格隐藏状态影响）
• INDEX(B:B,行号)：将区域动态锁定到B2至最后一行的有效数据范围，无需手动修改行号
• 原公式逻辑保留：SUBTOTAL(3,...)仅统计可见单元格（3对应COUNTA功能），--(B2:B="Defined")将符合条件的单元格转为数值1，最终通过SUMPRODUCT求和得到结果

简化写法（适用于Excel 365/2021及以上版本） #

如果使用支持动态数组的Excel版本，可直接用更简洁的公式：

=SUM(FILTER(SUBTOTAL(3,OFFSET(B2:B,ROW(B2:B)-ROW(B2),,1)),B2:B="Defined"))

这里B2:B会自动识别并扩展到列内最后一行数据。

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