So I have the following situation:

enter image description here

In the first column I have certain values, in the second column, given a certain row, I want it to contain the result of a function applied to all the preceeding rows (plus itself) in the 1st column. In this case the function applied is SUM().

The dumb approach is the one I used: write the formula in the first row, then copy&paste down till the row you want the function to be applied to.

I want it to be automated, though, and function with as many rows in the given column as there are, even if they are removed or added. I thought I'd use ARRAYFORMULA() for this, or MMULT() combined with TRANSPOSE() as I've seen doing in apparently related examples, but after quite a few tries I'm lost.

Does anybody know how it can be done?


3 Answers 3


The following Google Apps Script (GAS) will achieve your goal automatically:

function CUMSUM(array) {  
  // set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get active sheet
  var sh = ss.getActiveSheet();

  // get data (2D array)
  var aTest = sh.getRange(array).getValues();

  // convert 2D array to 1D array
  var aData = [].concat.apply([],aTest);

  // create new array to hold data
  var aResult = new Array(); 

  // itterate through data range  
  for(var i=0, len=aData.length; i<len; i++) {
    if(i==0) {
      aResult[i] = aData[0];
    } else {
      aResult[i] = aResult[i-1] + aData[i];

  // pass result to formula 
  return aResult;  

Add this script, via the script editor, to your spreadsheet and the function CUMSUM is available throughout the worksheet, like so: =CUMSUM("A2:A8"). Because an array is returned, use TRANSPOSE to get the right positioning.

See example file I've created: running total

  • 1
    Thanks for the reply, but that's not quite what I asked for. The fact my data is composed of sequential numbers and the fact I apply a sum to them is just for sake of example, but both the data and the function could be anything. Your approach is specific to the example case I gave, and doesn't even use the first column as a source of data, in fact ROW(A:A) expands to a sequence of increasing numbers which is independent of what A:A contains at all.
    – Fabio A.
    Jan 3, 2013 at 17:56
  • Oh, I see you've changed your response, now my comment above looks dumb. The GAS approach could be certanly reused for other other kind of operations (not just the sum, which I restate was just an example), but it wouldn't work for functions that are available only within the spreadsheet (think of slope() and intercept(), which are the ones I actually need). I would have to reimplement those functions in javascript.
    – Fabio A.
    Jan 4, 2013 at 1:47
  • @FabioA. Please show me an example were you use slope() and ` intercept()` (the dumb way). I'm glad I was able to get the running total. There are not many example's to find on the internet....
    – Jacob Jan
    Jan 4, 2013 at 5:21

Add this to all cells in column; it adds the above number to left number of cell. Doesn't use a script and can be applied to a column at once (just skip the first cell).


I think what you want may be:

for slope: =slope(A$1:A1,B$1:B1)
for intercept: =intercept(A$2:A2,B$2:B2)

though both the above need two sets of data, and based on your question:

for sum: =sum(A$2:A2)

in all cases copied down to suit (including into any added rows).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.