How to Get a Balance Column to Show the Balance Remaining in Each Row

CathyP712

I am trying to create a budget sheet which shows the remaining balance for each line. Here are my columns:

• Order: This column has the following formula =MATCH([Row ID]@row, [Row ID]:[Row ID], 0) to allow me to move around items to better fit the budget balance for each period.
• Row ID: This is the auto-numbered smartsheet number for each row (0001 is the 1st one)
• Description: This is a text field where I input the description of the expense or revenue.
• Date: This is a date column where I input the date of the expense, or revenue
• Amount: This is where I input the dollar amount of the expense (written as -$1.00) or revenue (written as $1.00)
• Balance: This is where I'm trying to have a running total of the current balance based on the "Amount" column, but I can't figure out the formula. I also want it to be a column formula so if I move a row to fix the budget, it will still calculate correctly.

Example: Basic Budget Flow

Order	Row ID	Description	Date	Amount	Balance
1	0001	Revenue 1	10/1/2024	$100.00	$100.00
2	0002	Expense 1	10/2/2024	-$25.00	$75.00
3	0003	Revenue 2	10/3/2024	$100.00	$175.00
4	0004	Expense 2	10/4/2024	-$25.00	$150.00
5	0005	Expense 3	10/5/2024	-$25.00	$125.00

There will be times when i will need to move expenses to avoid negative balances, and I would want the Balance column to accommodate that movement while still giving valid values. There are also times when I'll need to add additional expenses in between rows.

Example: Moved rows with proper Balance calculation.

Order	Row ID	Description	Date	Amount	Balance
1	0001	Revenue 1	10/1/2024	$100.00	$100.00
2	0002	Expense 1	10/2/2024	-$25.00	$75.00
3	0004	Expense 2	10/3/2024	-$25.00	$50.00
4	0003	Revenue 2	10/4/2024	$100.00	$150.00
5	0005	Expense 3	10/5/2024	-$25.00	$125.00

Example: Adding new Row

Order	Row ID	Description	Date	Amount	Balance
1	0001	Revenue 1	10/1/2024	$100.00	$100.00
2	0002	Expense 1	10/2/2024	-$25.00	$75.00
3	0006	Expense 4	10/2/2024	-$25.00	$50.00
4	0003	Revenue 2	10/3/2024	$100.00	$150.00
5	0004	Expense 2	10/4/2024	-$25.00	$125.00
6	0005	Expense 3	10/5/2024	-$25.00	$100.00

Is there a formula I can create for that balance column based on my requirements above?

Thanks in advance!

KPH

Hi @CathyP712

How about summing on the rows that have an order number equal to or less than the current row:

=SUMIF(Order:Order, <=Order@row, Amount:Amount)

That will give you a balance like this:

Then if you move rows, as the order is updated, it still works:

And if you add a row, again the order updates, and it still works:

Hope this helps.