具有多个OR/AND条件的SUMIFS(使用单元格引用) [英] SUMIFS with mutliple OR/AND criterias (using a cell reference)

问题描述

      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

在表中，您可以找到不同的 Products ( A列)和 Suppliers ( B列).

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

在 G1单元格中，如果满足以下条件，我想获取 C列中值的 sum :

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_01 或 Supplier_03

Product = Product_C AND
Supplier = Supplier_01 OR Supplier_03

这些条件在单元格E1:E3 中以条件1-3 键入.

为了实现这一目标，我尝试使用以下问题的解决方案(问题1 ， Q2 )，这给了我正确的结果:

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"}))

---

但是，此解决方案的问题是我需要以 {"Supplier_01"，"Supplier_02"} 手动输入 OR标准.
如何更改公式，以便可以引用 Cells E2:E3 中的值，所以如果用户更改了这些值，结果是否会自动调整?

---

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?

推荐答案

一种可能性:

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

对于A列和B列，以相同的方式扩展条件将很容易.

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

这篇关于具有多个OR/AND条件的SUMIFS(使用单元格引用)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！