将IF条件添加到SUMPRODUCT [英] Add IF-Condition to SUMPRODUCT

问题描述

  A B C D E F G H1产品日期销售标准1:Product_B结果:2002 Product_A 2020-04-15 500标准2:2020-04-153产品_B 2020-04-12 6004产品_B 2020-04-12 3005产品_B 2020-04-15 2006产品_B 2020-04-20 4007产品_C 2020-04-15 8008产品_C 2020-04-19 9009产品_C 2020-04-30 3001011 

在上表中，我在特定日期有不同的产品及其销售.
在 Cell G1 中，我根据 Cell E1 和 E2 中的条件计算销售额的总和.

  G1 = SUMPRODUCT(((($ A $ 2:$ A $ 100 = $ E $ 1)*($ B $ 2:$ B $ 100 = $ E $ 2)* $ C $ 2:$ C $ 100)) 

所有这些都完全按照应有的方式工作.

---

现在，我想包含一个 IF条件，该条件表示如果您在产品 Cell E1 中输入所有产品" SUMPRODUCT 中的条件($ A $ 2:$ A $ 100 = $ E $ 1)应该不应用.

因此，我尝试使用此方法:

  = SUMPRODUCT((IF(E1 ="All Products"，1，($ A $ 2:$ A $ 100 = $ E $ 1))*($ B $ 2:$ B $ 100 =($ E $ 2))* $ C $ 2:$ C $ 100)) 

不幸的是，仅当我在 Cell E1 中输入所有产品" 时，此解决方案才有效.
一旦我切换回 Product_B ，它会显示 0 而不是 200 .

要使其正常运行，我需要更改什么?

---

注意:

我知道一种解决方案是将 SUMPRODUCT 分成两个公式，如下所示:

  = IF(E1 ="All Products"，SUMPRODUCT(((($ B $ 2:$ B $ 100 = $ E $ 2)* $ C $ 2:$ C $ 100))，SUMPRODUCT((($ A $ 2:$ A $ 100 = $ E $ 1)*($ B $ 2:$ B $ 100 = $ E $ 2)* $ C $ 2:$ C $ 100))) 

但是，我希望使用一个 SUMPRODUCT -Formula的解决方案.

解决方案

使用SUMIFS:

  = SUMPRODUCT(SUMIFS($ C:$ C，$ A:$ A，IF(E1 ="All Products"，"*"，$ E $ 1)，$ B:$ B，$ E $ 2)) 

---

与您的

       A           B            C             D           E            F         G       H    
1  Products       Date        Sales     Criteria 1:   Product_B      Result:    200
2  Product_A    2020-04-15     500      Criteria 2:   2020-04-15    
3  Product_B    2020-04-12     600              
4  Product_B    2020-04-12     300              
5  Product_B    2020-04-15     200              
6  Product_B    2020-04-20     400              
7  Product_C    2020-04-15     800              
8  Product_C    2020-04-19     900              
9  Product_C    2020-04-30     300              
10
11

In the table above I have different products and their sales on a certain date.
In Cell G1 I calculate sum of the sales based on the criterias in Cell E1 and E2.

G1 = SUMPRODUCT((($A$2:$A$100=$E$1)*($B$2:$B$100=$E$2)*$C$2:$C$100))

All this works exactly as it should.

---

Now, I want to include an IF-Condition that says if you enter the words "All Products" into Cell E1 the product condition ($A$2:$A$100=$E$1) in the SUMPRODUCT should not be applied.

Therefore, I tried to go with this:

= SUMPRODUCT((IF(E1="All Products",1,($A$2:$A$100=$E$1))*($B$2:$B$100=($E$2))*$C$2:$C$100))

Unfortunately, this solution only works if I enter "All Products" into Cell E1.
Once I switch back to Product_B it displays 0 instead of 200.

What do I need to change to make it work?

---

NOTE:

I know one solution could be to split the SUMPRODUCT into two formulas like this:

=IF(E1="All Products",SUMPRODUCT((($B$2:$B$100=$E$2)*$C$2:$C$100)),SUMPRODUCT((($A$2:$A$100=$E$1)*($B$2:$B$100=$E$2)*$C$2:$C$100)))

However, I would prefer a solution with one SUMPRODUCT-Formula.

解决方案

With SUMIFS:

=SUMPRODUCT(SUMIFS($C:$C,$A:$A,IF(E1="All Products","*",$E$1),$B:$B,$E$2))

---

Combining with your LAST QUESTION

 =SUMPRODUCT(SUMIFS($C:$C,$A:$A,IF(E1="All Products","*",$E$1),$B:$B,$E$2:$E$3))

这篇关于将IF条件添加到SUMPRODUCT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！