SUMIFS标准不在范围内

本文介绍了SUMIFS标准不在范围内的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

ABCD 1 crit1 crit2 code amount 2 ab code1 10 3 ab code3 20 4 ab code2 100 5 ab code1 80 6 ab code4 30

此外，我还有一系列要排除的代码：

ABCD ........... 10排除行 11 code1 12 code8 解决方案

= SUMPRODUCT $ b（A2：A6 =a）*（B2：B6 =b）* ISNA（MATCH（C2：C6，A11：A12,0））， D2：D6）

MATCH 返回一个＃N / A！错误，当它找不到查找数组（代码）在查找数组（列表排除的行）。 ISNA（）将匹配转换为 FALSE 和不匹配（＃N / A！ / code>）到 TRUE 。然后使用数学运算符 * 与您的其他条件和 TRUE 成为1，而 FALSE 成为0。 SUMPRODUCT 终于总结了与1相符的金额（而不是0的金额）。

I want to calculate sum by multiple criterias for the table:

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

Also I have range of codes to exclude:

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

My formula for the marked result cell looks like this: SUMIFS(D2:D6,A2:A6,"a",B2:B6,"b"). How can I change my SUMIFS formula to filter by codes that are not in exclude codes range (A11:A12)?

解决方案

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

MATCH returns an #N/A! error when it can't find the lookup value (code) in the lookup array (list excluded rows). ISNA() converts matches to FALSE and non-matches (#N/A!) to TRUE. These are then "and'ed" with your other criteria using the mathematical operator *; TRUE becomes 1 and FALSE becomes 0 in the process. SUMPRODUCT finally sums up the amounts that line up with 1's (and not those with 0's).