Using the SUM Function for a Running Total in Excel

by Gregory on August 24, 2010

I’ve exported an iTunes playlist that shows the number of seconds per song and converted those seconds to a minutes:seconds format as you can see in column N in the spreadsheet below.

SUM Cumulative TIME

I now want a running summary in column O to see the total time after each song. The SUM Function works perfectly when combined with the right absolute/relative cell references in the function argument. Cell O2 has the following formula:


The argument is a range, indicated by the colon, from $N$2 to N2. The dollar signs indicate an absolute reference, meaning the first part of the range will not change as the formula is copied down. The second part of the range, N2, is a relative reference, which does change as the formula is copied down.

Absolute-Relative Reference SUM Function

I’ve not covered the absolute/relative cell reference before, but it’s not difficult to understand in this example.

The Cumulative Time in column O has a custom Time format, m:ss, to show the number of minutes and seconds. The spreadsheet example shows a total time of 52 minutes. Should the Cumulative time exceed 60 minutes, the Time format would have to be changed to h:mm:ss, to include hours.

Related Posts Plugin for WordPress, Blogger...

Comments on this entry are closed.

Previous post:

Next post: