代码之家 › 专栏 › 技术社区 › sqlbg

带OR AND的Sumifs

sumifs sum excel

sqlbg · 技术社区 · 8 年前

我有一个这样的数据集。。。

Group   Opportunity   Close Date    Stage   Yr Credited Probability Adjusted Credit
    Sigma   A           12/31/2016  Lost    2016          0.1
    Alpha   B           1/1/2016    Signed  2016          0.5
    Beta    C           7/26/2016   Review  2016          0.7
    Sigma   D - Loss    1/1/2016    Lost    2016          -1
    Alpha   E           12/31/2016  Review  2016          0.7
    Beta    F           1/25/2016   Pending 2016          0.6
    Sigma   G           12/31/2016  Review  2016          0.4
    Alpha   H           4/6/2014    Expired 2015          0.9
    Beta    I           5/14/2015   Pending 2015          0.1
    Sigma   J           12/31/2016  Review  2016          0.7
    Alpha   K - Loss    2/3/2016    Lost    2016          -0.5
    Beta    L           12/31/2016  Expired 2016          0.8
    Sigma   M           1/25/2016   Expired 2016          0.6
    Alpha   N           12/31/2016  Pending 2016          0.5
    Beta    O           12/31/2016  Pending 2016          0.4
    Sigma   P           12/31/2016  Pending 2016          0.3

我的部分目标是为我们提供一个带有AND和OR语句的SUMIFS函数。如果满足以下条件,则公式应得出每个组的概率调整学分总和:阶段=审查或两个阶段=损失且机会包含“损失”

该公式必须像SUMIFS公式一样运行,因为我有其他标准要结合。然而,这部分标准对我来说并不是那么直截了当。

2 回复 | 直到 8 年前

Scott Craner 8 年前

使用SUMPRODUCT():

=SUMPRODUCT((($D$2:$D$50 = "Review")+(($D$2:$D$50 = "Lost")*(ISNUMBER(SEARCH("Loss",$B$2:$B$50))))>0)*($F$2:$F$50))

使用数组公式时 AND 和 OR 使用操作数 * 和 + 分别地

编辑:

添加了分组:

=SUMPRODUCT((($D$2:$D$50 = "Review")+(($D$2:$D$50 = "Lost")*(ISNUMBER(SEARCH("Loss",$B$2:$B$50))))>0)*($F$2:$F$50)*($A$2:$A$50=H2))

user4039065 user4039065 8 年前

候补 SUMPRODUCT function 对于单个组。

=SUMPRODUCT((A$2:INDEX(A:A, MATCH("zzz",A:A ))=H2)*
                 SIGN((D$2:INDEX(D:D, MATCH("zzz",A:A ))="review")+
                 (D$2:INDEX(D:D, MATCH("zzz",A:A ))="lost")*
                 (RIGHT(B$2:INDEX(B:B, MATCH("zzz",A:A )), 4)="loss")),
             F$2:INDEX(F:F, MATCH("zzz",A:A )))

如果它有助于你理解它,你可以在公式中保留换行符。