SUMIFS with mutliple OR/AND criterias (using a cell reference)

user_4934150 0
Viewed 61
      A             B              C            D               E            F          G        E
1  Products     Suppliers        Value     Criteria 1:     Product_C      Result:      600
2  Product_A    Supplier_01      500       Criteria 2:     Supplier_01      
3  Product_B    Supplier_01      600       Criteria 3:     Supplier_03  
4  Product_B    Supplier_02      300                
5  Product_C    Supplier_01      200                
6  Product_C    Supplier_01      400                
7  Product_C    Supplier_03      800    
8
9

In the table you find a list of different Products (Column A) and Suppliers (Column B).

In Cell G1 I want to get the sum of the values in Column C if the following conditions are met:

Product = Product_C AND
Supplier = Supplier_01 OR Supplier_03

Those conditions are typed in as Criteria 1-3 in Cells E1:E3.

In order to achieve this I tried to go with the solution from these questions (Q1,Q2) which gives me the correct result:

G1 =SUM(SUMIFS($C:$C,$A:$A,$E$1,$B:$B,{"Supplier_01","Supplier_02"}))

However, my issue with this solution is that I need to enter the OR-criterias manually as {"Supplier_01","Supplier_02"}.
How do I have to change my formula so I can refer to the values in Cells E2:E3 so if the user changes those values the result is automatically adjusted?

excel-formula excel
3 Answers

One possibility:

=SUMPRODUCT((A2:A7=E1)*((B2:B7=E2)+(B2:B7=E3))*C2:C7)

It will be easy to extend criteria in the same fashion for both column A and B.

user_97581940

I was going to say that you need to transpose E2:E3. I think this is true in general, but in this particular case with only a single criterion applying to column A, you don't need to:

=SUM(SUMIFS($C:$C,$A:$A,$E$1,$B:$B,$E$2:$E$3))

works if entered as an array formula.

If you have multiple criteria for A and B, you do need to transpose one set of criteria:

=SUM(SUMIFS($C:$C,$A:$A,$E$1:$E$2,$B:$B,TRANSPOSE($E$3:$E$4)))

enter image description here

user_38949170

try this

=SUM(SUMIFS($C:$C,$A:$A,$E$1,$B:$B,E2:E3))

or

=SUM(IF(($A$2:$A$7=E1)*(($B$2:$B$7=E2)+($B$2:$B$7=E3)),$C$2:$C$7,0))
user_85221550
Related