如何修改SUBTOTAL嵌套SUMIF公式适配表格筛选动态结果？

原公式的问题在于SUMIF会计算整个表格中符合条件的所有行，不受筛选状态影响，外层的SUBTOTAL无法改变这一点。以下是两种可行的修改方案：

方案1：适用于所有Excel版本（包括旧版） #

使用SUMPRODUCT结合SUBTOTAL来识别可见行并计算：

=SUMPRODUCT(SUBTOTAL(3,OFFSET(table1[quantity],ROW(table1[quantity])-MIN(ROW(table1[quantity])),0,1)),--(table1[product]=E6),table1[quantity])

• 解释：
  • SUBTOTAL(3,OFFSET(...))：逐行判断该行是否为可见行（可见行返回1，隐藏行返回0）
  • --(table1[product]=E6)：将产品匹配条件转换为0/1的数值（匹配返回1，不匹配返回0）
  • SUMPRODUCT：将三个数组对应元素相乘后求和，最终只计算可见且符合产品条件的数量总和

方案2：适用于Excel 365/2021及以上版本（支持动态数组） #

利用FILTER筛选符合条件的行，再用SUBTOTAL计算可见行的和：

=SUBTOTAL(9,FILTER(table1[quantity],table1[product]=E6))

• 解释：
  • FILTER(table1[quantity],table1[product]=E6)：先筛选出所有产品匹配E6的数量值
  • SUBTOTAL(9,...)：对筛选后的结果求和，自动忽略被筛选隐藏的行，实现结果随筛选状态动态变化

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