Running Total

Chard

Hi,

I am quite new to Smartsheets and trying to set up a running total on a sheet that adds up the ongoing numbers input into another column. I have looked at some of the other posts on this topic

but cannot seem to get mine to work. I have input the totals manually to show what I am after. Help!

rrenee

Hello @Chard,

I came up with two possible solutions, one more simple, and one more complex (but better in the long run).

1.The first method finds the total sum of the cell above it and the new quantity added to that row, and you drag the formula down like you would in excel:

2. The second method requires an ongoing row number helper column, but the running total is auto-calculated and less prone to breaking:

The first thing you'll want to do is make the row number column —the row number formula does still need to be manually dragged down to new rows, and can go beyond the data as it counts if cells are both blank or populated.

Once that is done, the running total will auto-calculate using the row number column, and can be converted to a column formula so you never have to edit it again.

Row Number:

=COUNTIFS([Quantity Made]$1:[Quantity Made]@row, OR(ISBLANK(@cell ), NOT(ISBLANK(@cell ))))

• by @Paul Newcome

Running Total:

=IF([Quantity Made]@row = 0, "", SUMIFS([Quantity Made]:[Quantity Made], [Row Num]:[Row Num], @cell <= [Row Num]@row))

I hope this helps!

Paul Newcome

You can make the row number formula a column formula by starting with an auto-number type column and then using this column formula in a text/number column:

=MATCH([Auto-Number Column Name]@row, [Auto-Number Column Name]:[Auto-Number Column Name], 0)

rrenee

Hello @Chard,

I came up with two possible solutions, one more simple, and one more complex (but better in the long run).

1.The first method finds the total sum of the cell above it and the new quantity added to that row, and you drag the formula down like you would in excel:

2. The second method requires an ongoing row number helper column, but the running total is auto-calculated and less prone to breaking:

The first thing you'll want to do is make the row number column —the row number formula does still need to be manually dragged down to new rows, and can go beyond the data as it counts if cells are both blank or populated.

Once that is done, the running total will auto-calculate using the row number column, and can be converted to a column formula so you never have to edit it again.

Row Number:

=COUNTIFS([Quantity Made]$1:[Quantity Made]@row, OR(ISBLANK(@cell ), NOT(ISBLANK(@cell ))))

• by @Paul Newcome

Running Total:

=IF([Quantity Made]@row = 0, "", SUMIFS([Quantity Made]:[Quantity Made], [Row Num]:[Row Num], @cell <= [Row Num]@row))

I hope this helps!

Paul Newcome

You can make the row number formula a column formula by starting with an auto-number type column and then using this column formula in a text/number column:

=MATCH([Auto-Number Column Name]@row, [Auto-Number Column Name]:[Auto-Number Column Name], 0)

Chard

Hi @rrenee, Thank you so much for your help on this. Used both, and worked a treat! 😀