Running total (sum everything above or below)

Kevin Ikeda

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?

Paul Newcome

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.

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?

Kelly

Kevin Ikeda

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

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?

Kevin Ikeda

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

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

Paul Newcome

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.

Kevin Ikeda

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

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.