基于Material筛选的多条件动态求和公式需求

需求说明 #

需要实现：根据Material列的筛选结果，动态汇总该列所有被选中Material对应的全部Valor1值（即使部分关联行因其他筛选条件隐藏）。例如筛选单个Material（如Mat1）时返回总和10，筛选多个Material时汇总对应所有值，而SUBTOTAL(109)仅能汇总可见行，无法满足此需求。

方案1：适用于Excel 365/2021（支持动态数组） #

使用FILTER+UNIQUE+SUMIF组合公式，逻辑清晰且简洁：

=SUM(SUMIF(A:A, UNIQUE(FILTER(A2:A10, SUBTOTAL(103, OFFSET(A2, ROW(A2:A10)-ROW(A2), 0)))), B:B))

公式拆解：

1. SUBTOTAL(103, OFFSET(A2, ROW(A2:A10)-ROW(A2), 0))：判断A2:A10中每一行是否为筛选后可见行（103代表忽略隐藏行的COUNTA函数，返回1表示可见，0表示隐藏）
2. FILTER(A2:A10, ...)：提取所有可见行的Material值
3. UNIQUE(...)：去除重复的Material值，得到当前筛选的唯一Material列表
4. SUMIF(A:A, ..., B:B)：对每个唯一Material，汇总整个数据中对应的所有Valor1值
5. SUM(...)：将多个Material的汇总结果相加，得到最终总和

方案2：兼容旧版Excel（无动态数组功能） #

使用SUMPRODUCT+COUNTIF+SUMIF组合公式：

=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10, A2:A10&"")*(SUBTOTAL(103, OFFSET(A2, ROW(A2:A10)-ROW(A2), 0))>0)*SUMIF(A:A, A2:A10, B:B))

公式拆解：

1. (A2:A10<>"")/COUNTIF(A2:A10, A2:A10&"")：对重复的Material值去重，每个唯一Material仅保留一个有效标记
2. SUBTOTAL(103, ...)>0：筛选出可见行的Material标记
3. SUMIF(A:A, A2:A10, B:B)：汇总每个Material对应的全部Valor1值
4. SUMPRODUCT：将符合条件的汇总值相乘后累加，得到最终总和

使用注意事项 #

• 替换公式中的A2:A10为你的Material数据范围，B:B为Valor1列的完整范围
• 若表头包含筛选器，确保数据范围从第一行数据开始（而非表头行）

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