I'm trying to combine these 3 Sumifs into 1 column formula...is this possible?

Options

Best Answer

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Tina T

    If I understand correctly, you would like one of these three formulas to be applied, depending upon different conditions? I'm going to take a guess at what you are trying to do.

    =IF([Deal Type]@row = "Change Order", SUMIFS([TCV 100%]@row * 0.03, [Deal Type]@row, "Change Order"), IF(AND([Deal Type]@row = "New GL Deal", OR([Partner Medallion Status]@row = "Silver", [Partner Medallion Status]@row = "Business Partner")), SUMIFS([TCV 100%]@row * 0.08, [Deal Type]@row, "New GL Deal", [Partner Medallion Status]@row, OR(@cell = "Silver", @cell = "Business Partner")), IF(AND([Deal Type]@row = "New GL Deal", OR([Partner Medallion Status]@row = "Silver", [Partner Medallion Status]@row = "Business Partner")), SUMIFS([TCV 100%]@row * 0.04, [Deal Type]@row, "New GL Deal", [Partner Medallion Status]@row, OR(@cell = "Gold", @cell = "Platinum")))))

    You should be able to copy paste this formula directly into your sheet. The IF statements differentiate which of the SUMIFs you want to execute under specific circumstances.

    Does this work for you?

    Kelly

  • Tina T
    Tina T ✭✭
    Options

    HI Kelly, thank you...that partially worked. The Change Order lines are calculating with this formula, but the New GL Deal lines still aren't calculating.

    I'm going to play around a bit with what you have and see if I can get those other lines to calculate.

    Thank you again! :)

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey

    If you give me an example of what the formula produced vs what was expected, I can help you troubleshoot, if needed. Shout out to me if you need help.

    Kelly

  • Tina T
    Tina T ✭✭
    Options

    Thank you again Kelly, I figured it out...2nd IF statement I just had to change the partner medallion status to Platinum and Gold :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!