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.

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:

=SUM($N$2:N2)

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.

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.

Comments on this entry are closed.