# How do I add cells in google sheets to make a total specifically using previous entries?

I'm not sure how to word this, but im creating a pilot episode for a film I'm making and I am not sure what function(s) to do so that when I add a duration to a specicfic shot, it updates a row down and a column right to it (adding to the time stamp of the episode). Anybody have any ideas?

# SCAN Formula

Make sure the number format for all relevant columns is set to `Duration` and place the following formula in B2.

``````={0; (SCAN(0,TOCOL(A2:A,1),
LAMBDA(s, n, s+n)))}
``````
• The SCAN function is perfect for this task. It returns the intermediary calculations from a LAMBDA function that adds `s` an accumulator (subtotal) to `n` a new number.
• variable names are arbitrary, use whatever you prefer.
• TOCOL is used because it can remove empty rows. In this way, the entire column A can be specified as the range allowing the formula to expand dynamically as new data is added.
• An initial duration of `0` is added to the array to offset it because you want starting, not ending, timestamps.

# Both Starting and Ending Timestamps

The formula can be easily adapted to return a two-column array of starting and ending timestamps in a single formula, since it is essentially the same formula, simply offset by one row:

``````=LET(end,SCAN(0,TOCOL(A2:A,1),
LAMBDA(s, n, s+n)),
{{0;end},{end;""}})
``````
• LET is used to store the result of
``````SCAN(0,TOCOL(A2:A,1),
LAMBDA(s, n, s+n))
``````
in the variable `end`
• an array is returned where
• column 1 is `end` with a `0` prepended as the first row
• column 2 is `end` with a blank value appended as the last row

# Functions

A simple `= P3 + P4` in the Q4 should work. Q3 doesn't need a formula; it'll just be 0:00:00. Just drag down the formula by the bottom-right square and it'll add it up for you.