Running total (sum everything above or below)

I am trying to create a running total where each row is a separate order. The order has a column for cycle time and another column for cumulative time where it sums up all of the cycle times of the orders before it in line. Ex. an order submitted through a form goes onto row 50. It has a cycle time of 10 min, and also returns the 10min + (sum of cycle time in rows 1-49). Is there an easy way to do this that I am missing?

Best Answer


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Kevin Ikeda

    One solution uses a helper column - the system generated autonumber column. The default name for this column is [Row ID]

    =SUMIFS([your cycle time column]:[your cycle time column], [Row ID]:[Row ID], @cell <= [Row ID]@row)

    This will sum the cycle time column (be sure to change the formula to use your actual column name) where the [Row ID] is less than or equal to the row you're sitting in. If you need to further filter to only sum the rows that have the same Order Number, that criteria can be added to the SUMIFS formula.

    You can find more information on SUMIFS here

    Will this work?


  • Hey @KDM !

    Thanks for input, I was not able to get that to work. When I try to just use the [Row ID] reference it gives me an error. Ex the formula =[Row ID]@row returns an unparseable. I would expect that to return the row number of the row im on correct? Is there some syntax or setting im missing?



  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey Kevin

    Ok, let's troubleshoot. Yes you would expect to see the row number. 1) Did you physically insert the new helper column - the system generated auto-numbering column?

  • Hey KDM,

    ah ok I see. No I did not, but unfortunately that will not work out of the box as I need to be able to move rows around to set priorities (with top priority in row 1). Would it be possible to have that column update automatically to match its position in the sheet? if I drag row 40 up to the top then it returns row ID = 1?

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey Kevin

    I found this post

    It uses two helper columns. One has the number 1 in every cell. This column was called 'index'. I would populate this using a formula

    =IF([some column that is never blank]@row<>"", 1)

    This will only number non blank rows and could be set as a column formula. If your sheet has the system Created or Modified columns, either of those would work. Perhaps your primary column is never blank. [Row ID] is never blank if you choose to keep that column on your sheet.

    The 2nd helper column, which the post above calls 'Priority' (very appropriate for your needs) is what you would use as your max.

    =IF(Index@row <> "", SUM(Index$1:Index@row)).

    Would this work for you?


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    If you add in the [Row ID] column, you can then insert a text/number column and use this as a column formula:

    =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

    This new text/number column will always have 1 in the first row, 2 in the second row, so on and so forth regardless of sorting or moving rows.

  • Thank you both! the match row ID worked! I can now use the sumif function KDM mentioned to sum it all up. thanks again!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    That's exactly what I would have done after setting up the row number column, so I figured I would just leave that portion alone. Glad you were able to get it working.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!