SUMIFS with criterion not in range

问题:

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)?

回答1:

=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).

回答2:

Jean-François Corbett's answer is by far the most appropriate and comprehensible here.

However, just to show that it can be still be achieved using SUMIFS (although at the cost of some convolutedness!):

=SUM(MMULT(MMULT({1,1},SUMIFS(D2:D6,A2:A6,"a",B2:B6,"b",C2:C6,{"=","<>"}&A11:A12)),{-1;1}))/2