excel - 标准不在范围内的 SUMIFS

Question

我想根据表格的多个条件计算总和:

    A      B      C    D
1  crit1 crit2 code   amount
2   a      b    code1  10
3   a      b    code3  20
4   a      b    code2  100
5   a      b    code1  80
6   a      b    code4  30

我还有一些要排除的代码:

      A         B    C    D
     ...........
10  exclude rows
11  code1
12  code8

标记结果单元格的公式如下所示:SUMIFS(D2:D6,A2:A6,"a",B2:B6,"b")。如何更改我的 SUMIFS 公式以按不在排除代码 范围 (A11:A12) 中的代码进行过滤？

Answer 1

=SUMPRODUCT(
            (A2:A6="a")  * (B2:B6="b") * ISNA(MATCH(C2:C6,A11:A12,0)),
            D2:D6)

MATCH 在查找数组(列出排除的行)中找不到查找值(代码)时返回 #N/A! 错误。 ISNA() 将匹配项转换为 FALSE，将不匹配项 (#N/A!) 转换为 TRUE。然后使用数学运算符 * 将这些与您的其他条件“相加”； TRUE 在此过程中变为 1，FALSE 变为 0。 SUMPRODUCT 最终将与 1 对齐(而不是与 0 对齐的金额)相加。