RemainingBalance to show on Source Sheet

Options

Hello All! What is the best way to maintain a remaining balance? In my scenario, I want to show the remainng balance after each new invoice record.

INVOICE SHEET

I tried the following in the Remaining Balance field:

=[PO# Award Amntt]@row - SUMIFS([Inv. Amnt]:[Inv. Amnt], Vendor:Vendor, =Vendor@row, [FYar]:[FY], =[FY]@row, [P.O. #]:[P.O. #], =[P.O. #]@row)

This is what I get:


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Tnorman

    Thank you for clarifying! In this case, we would need to change your ranges to only look from the top row of the sheet to the current row, instead of looking through the entire column.

    =[PO# Award Amntt]@row - SUMIFS([Inv. Amnt]$1:[Inv. Amnt]@row, Vendor$1:Vendor@row, =Vendor@row, [FYar]$1:[FY]@row, =[FY]@row, [P.O. #]$1:[P.O. #]@row, =[P.O. #]@row)

    Then you can drag-fill this down the column so it applies to all the cells. Note that you won't be able to make this a column formula due to locking the top bit of the formula to the first row with $1. However, formulas will still auto-fill even if they're not a column formula - see: Use or Override Automatic Formatting and Formula Autofill

    Let me know if this is better!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Tnorman

    This formula looks to be correct! It's subtracting the total Invoice Amount has has come in for that PO across multiple rows from the current "PO # Award Ammnt" cell in this current row.

    However, do you mean that each new row has a new Award Amount that you need to SUM as well?

    In that case, you'll need another SUMIFS at the beginning:

    =SUMIFS([PO# Award Amntt]@row:[PO# Award Amntt]@row, Vendor:Vendor, =Vendor@row, [FYar]:[FY], =[FY]@row, [P.O. #]:[P.O. #], =[P.O. #]@row) - SUMIFS([Inv. Amnt]:[Inv. Amnt], Vendor:Vendor, =Vendor@row, [FYar]:[FY], =[FY]@row, [P.O. #]:[P.O. #], =[P.O. #]@row)


    Let me know if this is what you were looking to do or not!

    Cheers,

    Genevieve

  • Tnorman
    Options

    Hello Genevieve,

    Thanks for responding, I was hoping to show the Remaining balance decrementing values over time. However once a row comes in all PREVIOUS rows matching the criteria gets updated to reflect the "CURRENT" Remaining Balance amount.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Tnorman

    Thank you for clarifying! In this case, we would need to change your ranges to only look from the top row of the sheet to the current row, instead of looking through the entire column.

    =[PO# Award Amntt]@row - SUMIFS([Inv. Amnt]$1:[Inv. Amnt]@row, Vendor$1:Vendor@row, =Vendor@row, [FYar]$1:[FY]@row, =[FY]@row, [P.O. #]$1:[P.O. #]@row, =[P.O. #]@row)

    Then you can drag-fill this down the column so it applies to all the cells. Note that you won't be able to make this a column formula due to locking the top bit of the formula to the first row with $1. However, formulas will still auto-fill even if they're not a column formula - see: Use or Override Automatic Formatting and Formula Autofill

    Let me know if this is better!

    Cheers,

    Genevieve

  • Tnorman
    Options

    Geneview - Awesome approach!! Let me try this.

  • Tnorman
    Options

    This worked perfectly!!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Wonderful! I'm so glad to hear that 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!