SUM depending on whether one of the columns has a value

hi - I'm trying to write a formula to calculate the "Over or Under Budget" column to show if we've over or under spent. If the "Reforecast Budget" column is $0 (meaning, there is no reforecast budget), then it should calculate "Original Budget - Projected Total Cost." But, if the Reforecast Budget column has a value, it should calculate "Reforecast Budget - Projected Total Cost." In other words, I want to calculate the over/under column on either the original budget OR the reforecast budget if there is one. Any idea how I can do this?



Best Answer

  • bruceh
    bruceh ✭✭
    Answer ✓

    Thanks very much for the answers! I actually saw John's reply first, and played with it a bit. What I finally came up with was actually closer to Aravind's solution. Thanks again for your replies....they are HUGELY helpful. This is what I used:

    =IF([Reforecast Budget]@row>0, [Reforecast Budget]@row - [Projected Total Cost]@row, [Original Budget]@row - [Projected Total Cost]@row)

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭

    Hi @bruceh


    In the Over or Under Budget column, use this formula. =IF([Reforecast Budget]@row = 0, [Original Budget]@row - [Projected Total Cost]@row, [Reforecast Budget]@row - [Projected Total Cost]@row)

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • Try something like this:

    IF(

    OR(

    value([Reforecast Budget]@row) = 0,ISBLANK([Reforecast Budget]@row)

    ),

    [Reforecast Budget]@row - [Projected Total Cost]@row,

    [Original Budget]@row - [Projected Total Cost]@row

    )

  • bruceh
    bruceh ✭✭
    Answer ✓

    Thanks very much for the answers! I actually saw John's reply first, and played with it a bit. What I finally came up with was actually closer to Aravind's solution. Thanks again for your replies....they are HUGELY helpful. This is what I used:

    =IF([Reforecast Budget]@row>0, [Reforecast Budget]@row - [Projected Total Cost]@row, [Original Budget]@row - [Projected Total Cost]@row)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!