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 149). Is there an easy way to do this that I am missing?
Best 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.
Answers

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?
Kelly

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?
thanks!
Kevin

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 autonumbering 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?

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?
Kelly

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!

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
Categories
Check out the Formula Handbook template!