Need formula & function for the question below


If I apply for PTO, I want to make sure it's getting subtracted from the "WR Balance Before Application" and it would be straight subtraction selecting cells & it's sorted. The main formula I am looking for is, If I apply again, my "WR Balance Before Application" should be the latest "WR Balance Left After Application" from my last entry. How can I do that? Immediate help would be a great help and appreciated.


  • PCG Sam Harwart
    PCG Sam Harwart ✭✭✭✭
    edited 05/07/24

    Hi @Neeraj Chavan ,

    Perhaps you could have [WR Balance Before Application] be the total WR balance less any [Approved in HRMS] and the [WR Balance Left After Application] could be the total WR balance before application less any pending requests. Where does the WR balance come from?

    1. [WR Balance Before Application]: =[WR Balance]@row - SUMIF([Approved in HRMS]:[Approved in HRMS], 1, [WR Applied]:[WR Applied])
    2. [WR Balance Left After Application]: =[WR Balance Before Application]@row - SUMIF([Approved in HRMS]:[Approved in HRMS], 0, [WR Applied]:[WR Applied])

    What formulas are you using in there now?




    - | Smartsheet's 2023 Partner of the Year for North America
    Want to chat about a Smartsheet problem you're facing? Grab time on my calendar here: Schedule a Discovery Call!

  • Brian_Richardson
    Brian_Richardson Overachievers

    If I'm understanding this correctly, you expect multiple rows for one person? So in the example you may have 1 row for Agrima with WR Balance Before Application = 10 and WR Balance After Application = 9.5 then a second row further down where you want Agrima to show WR Balance Before Application = 9.5 (balance from earlier row) and then subtract a value from that?

    I'll show how to do this below, but I'd actually suggest restructuring to make this simpler. If you have 1 sheet with all the names on it, and a separate sheet with the actual PTO changes (adds/removes) on it, then it becomes simple to have a cross-sheet reference formula that simply adds the value for each person.

    = SUMIF({Name} , Name@row , {WR Applied} )

    If instead you want to build a total down the sheet, you can do this using a couple of helper columns.

    Setup an AutoNumber column called "Auto"

    Setup a Text/Number column called "WR Applied to Date" . In this column add the following column formula:

    =SUMIFS([WR Applied]:[WR Applied], Auto:Auto, <=Auto@row, Name:Name, Name@row)

    This will add up a total WR applied, up to and including that row, but not the rows further down. You can then subtract that total applied from your balance.




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!