SUMPRODUCT函数统计可见行失效，请求排查公式问题

你的公式返回结果未随筛选变化，大概率是OFFSET作为易失性函数，在SUMPRODUCT的数组计算中未能正确识别自动筛选的隐藏行。以下是两种可靠的修正方案：

方案一：修正原SUMPRODUCT公式 #

明确OFFSET的高度和宽度参数，或改用非易失性的INDEX函数：

优化OFFSET版本 #

=SUMPRODUCT((J7:J1000="Yes")*(SUBTOTAL(103,OFFSET(J7,ROW(J7:J1000)-ROW(J7),0,1,1))))

通过添加1,1明确指定偏移后的单元格为单个单元格，确保SUBTOTAL能准确判断每行的可见性。

INDEX替代版本（更稳定） #

=SUMPRODUCT((J7:J1000="Yes")*(SUBTOTAL(103,INDEX(J7:J1000,ROW(J7:J1000)-ROW(J7)+1,1))))

INDEX是非易失性函数，不会因Excel重计算导致数组迭代异常，能更稳定地配合SUBTOTAL识别可见行。

方案二：改用AGGREGATE函数（更简洁） #

直接使用AGGREGATE函数，无需嵌套复杂的OFFSET/SUBTOTAL组合：

=AGGREGATE(2,5,(J7:J1000="Yes")*1)

• 参数2代表COUNT统计
• 参数5代表忽略自动筛选隐藏的行
• (J7:J1000="Yes")*1将布尔判断结果转换为1（Yes）或0（非Yes）的数组，AGGREGATE会自动统计可见行中1的数量。

原公式失效原因 #

OFFSET是易失性函数，在部分Excel版本或多列筛选场景下，SUMPRODUCT对OFFSET生成的数组进行迭代时，可能无法正确关联筛选后的行状态，导致SUBTOTAL始终返回1（认为所有行可见），最终统计结果未随筛选变化。

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