RemainingBalance to show on Source Sheet

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

image.png

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:

image.png


Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

Answers

  • 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

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

  • 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
    Answer ✓

    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

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

  • Geneview - Awesome approach!! Let me try this.

  • This worked perfectly!!

  • Wonderful! I'm so glad to hear that 🙂

    Need more information? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!