CODEWITHSUNDEEP
excelsumsumifs
sqlbg
sqlbg

Reputation: 73

Sumifs with OR AND

I have a dataset that looks like this...

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

Part of my goal is to us a SUMIFS function with an AND and OR statement. The formula should yield the sum of probability adjusted credits per Group if the following condition is met: Stage = Review OR BOTH Stage = Lost AND Opportunity contains "Loss"

The formula must function like a SUMIFS formula because I have other criteria to incorporate. However, this part of the criteria is not as straight forward to me.

Upvotes: 1

Views: 529

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

Use SUMPRODUCT():

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

enter image description here

When using Array formulas for AND and OR use the operands * and + respectively.

Edit:

Added the grouping:

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

enter image description here

Upvotes: 3

user4039065
user4039065

Reputation:

Alternate SUMPRODUCT function for individual groups.

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

You can keep the line feeds in that formula if it helps you to make sense of it.

  alpha_beta_sigma

The SUMPRODUCT function does not play well with AND / OR functions. Use + for OR and * for AND with appropriate bracketing to provide hierarchy.

Upvotes: 2

Related Questions