SUMIFS标准不在范围内 [英] SUMIFS with criterion not in range

问题描述

  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 
   SUMIFS（D2：D6，A2：A6，a，B2：B6，p $ p 
 
 < b）。 
如何更改我的 SUMIFS 公式，以过滤不在排除代码的代码范围（ A11 ：A12 ）？
解决方案
 
  = 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).



                        
这篇关于SUMIFS标准不在范围内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！