# Cumulative Sum without Script

For this question, I've created a Google Apps Script function, that calculates the cumulative sum of a range. Martin Hawksey on Google+ made a comment about a more efficient way of calculating the cumulative sum:

function cumulativeSum(array){
var output = [];
for (i in array){
if(i==0) {
if (array[i].length > 1) throw ("Only single column of data");
output.push([array[i][0]]);
} else {
output.push([output[i-1][0] + array[i][0]]);
}
}
return output;
}

My question is: can this be achieved with the usage of formula's?

• Apologies for a very late answer, I just saw he thread in "Related" on the right. Commented Jun 30, 2013 at 10:22

Yes:

=ArrayFormula(SUMIF(ROW(A1:A10);"<="&ROW(A1:A10);A1:A10))

The numbers in the green circle are the ones being summed, the ArrayFormula is populating all the others based on the cell it was pasted into, in this example I pasted in A11, B1, and C1 to show its effect.

This spreadsheet that I made a while ago attempts to explain how MMULT can be used for these sort of problems, and it also demonstrates the SUMIF solution on one of the sheets, which can (and probably should) be used for special cases like the one in this question.

• I took this formula once (I think it was yours) on the Google Forum: SUMIF(ROW(A:A);"<"&ROW(A:A)), that didn't work. This one does. Commented Jun 30, 2013 at 11:56
• The image in the answer was edited in by another member: +Xavier Casto Commented Jul 4, 2013 at 0:10
• @Fogest, I used a product called "Snagit" find out more about at techsmith.com/download/snagit Commented Jul 5, 2013 at 15:49
• @XavierCasto Oh, I've used this a LONG time ago, didn't remember it having this, though it was a long time ago. Thanks for the reply though, I like products by techsmith. Commented Jul 5, 2013 at 15:50

Assuming you want:

Row | A | B | B's formula
--------------------------------
1  | 1 | 1 | =1     (or =A1)
2  | 2 | 3 | =1+2   (or =A2+B1)
3  | 3 | 6 | =1+2+3 (or =A3+B2)

etc...

Then you can use:=sum(\$A\$1:A1) where A1 is the start of numbers to sum. Put that formula in B1 (or where ever you want) and fill it down, and it will sum all cells up to and including the row you're on. Alternately, you can use the =Ax+Bx-1 (x = current row, data in A, cumulative sum in B) format from the "B's formula" section above.

• This seems to work and is much simpler than the accepted answer Commented Mar 15, 2017 at 6:18
• agreed, this is simpler and it works seamless! Commented May 29, 2018 at 21:40

While the accepted answer is on spot it has a major disadvantage due to the fact that the last parameter of SUMIF needs to be always a "valid range" so when it comes to some more advanced running total calculation MMULT is the better option:

=ARRAYFORMULA(IF(LEN(A1:A),
MMULT(TRANSPOSE((ROW(A1:A)<=TRANSPOSE(ROW(A1:A)))*A1:A), SIGN(A1:A)^2), ))

The accepted answers are far too complicated for running tallies. A much easier answer is:

=SUM(A\$1:A1)

Substitute the A for the column you want. Substitute 1 for the row you want. Click the small box in the bottom right corner of the cell and drag it down to auto tally the cells you chose.

Note: I know when this was asked that my solution might not have been available but in 2020 the title 'Cumulative Sums without Scripts' makes this a leading google result for the query 'running totals in google sheets' and all the Google results had long complicated answers.

## Sumif

=INDEX(
IF(
A3:A = "", ""        ,SUMIF(
ROW(
A3:A
), "<=" &
ROW(
A3:A
) ,A3:A
)
)
)

Drawbacks:

• the last parameter of SUMIF needs to be always a "valid range"
• not easy to add new cummulative sum by key, add complex conditions

## Mmult

=INDEX(IF(A3:A="""","""" ,
MMULT
(--(TRANSPOSE(
ROW(A3:A) <= TRANSPOSE(ROW(A3:A))))
, -- A3:A)))

Option suggested by User0.

Drawbacks:

## Manual formulas

Row | A | B | B's formula
--------------------------------
1  | 1 | 1 | =1     (or =A1)
2  | 2 | 3 | =1+2   (or =A2+B1)
3  | 3 | 6 | =1+2+3 (or =A3+B2)

Drawbacks:

• will not refresh automatically, have to preserve space for the next entries.

## Script

My try is to use a custom formula:

=runningTotal(A3:A)

Sample file

Source code

Code:

/**
* Get running total for the array of numbers
* by [email protected]
*
* @param {array} numbers The array of numbers
* @param {number} total_types (1-dafault) sum, (2) avg, (3) min, (4) max, (5) count;
*                  1-d array or number
* @param {number} limit number of last values to count next time.
*                 Set to 0 (defualt) to take all values
* @param {array} keys (optional) array of keys. Function will group result by keys
* @return The hex-code of cell background & font color
* @customfunction
*/
function runningTotal(numbers, total_types, limit, keys) {

// possible types to return
var oTypes = {
'1': 'sum',
'2': 'avg',
'3': 'min',
'4': 'max',
'5': 'count'
}
// checks and defaults
var errPre = '🥴 ';
if( typeof numbers != "object" ) {
numbers = [ [numbers] ];
}
total_types = total_types || [1];
if( typeof total_types != "object" ) {
total_types = [ total_types ];
}
if( keys && typeof keys != "object" ) {
keys = [ [keys] ];
}
if (keys) {
if (numbers.length !== keys.length) {
throw errPre + 'Numbers(' +
numbers.length +
') and keys(' +
keys.length +
') are of different length'; }
}
// assign types
var types = [], type, k;
for (var i = 0; i < total_types.length; i++) {
k = '' + total_types[i];
type = oTypes[k];
if (!type) {
throw errPre + 'Unknown total_type = ' + k;
}
types.push(type);
}
limit = limit || 0;
if (isNaN(limit)) {
throw errPre + '`limit` is not a Number!';
}
limit = parseInt(limit);

// calculating running totals
var result = [],
subres = [],
nodes = {},
key = '-',
val;
var defaultNode_ = {
values: [],
count: 0,
sum: 0,
max: null,
min: null,
avg: null,
maxA: Number.MIN_VALUE,
maxB: Number.MIN_VALUE,
maxC: Number.MIN_VALUE,
minA: Number.MAX_VALUE,
minB: Number.MAX_VALUE,
minC: Number.MAX_VALUE
};
for (var i = 0; i < numbers.length; i++) {
val = numbers[i][0];
// find correct node
if (keys) { key = keys[i][0]; }
node = nodes[key] ||
JSON.parse(JSON.stringify(defaultNode_));
/**
* For findig running Max/Min
* sourse of algorithm
* https://www.geeksforgeeks.org
* /sliding-window-maximum-maximum-of-all-subarrays-of-size-k/
*/
// max
//reset first second and third largest elements
//in response to new incoming elements
if (node.maxA<val) {
node.maxC = node.maxB;
node.maxB = node.maxA;
node.maxA = val;
} else if (node.maxB<val) {
node.maxC = node.maxB;
node.maxB = val;
} else if (node.maxC<val) {
node.maxC = val;
}
// min
if (node.minA>val) {
node.minC = node.minB;
node.minB = node.minA;
node.minA = val;
} else if (node.minB>val) {
node.minC = node.minB;
node.minB = val;
} else if (node.minC>val) {
node.minC = val;
}

// if limit exceeds
if (limit !== 0 && node.count === limit) {
//if the first biggest we earlier found
//is matching from the element that
//needs to be removed from the subarray
if(node.values[0]==node.maxA) {
//reset first biggest to second and second to third
node.maxA = node.maxB;
node.maxB = node.maxC;
node.maxC = Number.MIN_VALUE;
if (val <= node.maxB) {
node.maxC = val;
}
} else if (node.values[0]==node.maxB) {
node.maxB = node.maxC;
node.maxC = Number.MIN_VALUE;
if (val <= node.maxB) {
node.maxC = val;
}
} else if (node.values[0]==node.maxC) {
node.maxC = Number.MIN_VALUE;
if (val <= node.maxB) {
node.maxC = val;
}
} else if(node.values[0]==node.minA) {
//reset first smallest to second and second to third
node.minA = node.minB;
node.minB = node.minC;
node.minC = Number.MAX_VALUE;
if (val > node.minB) {
node.minC = val;
}
}
if (node.values[0]==node.minB) {
node.minB = node.minC;
node.minC = Number.MAX_VALUE;
if (val > node.minB) {
node.minC = val;
}
}
if (node.values[0]==node.minC) {
node.minC = Number.MAX_VALUE;
if (val > node.minB) {
node.minC = val;
}
}
// sum
node.sum -= node.values[0];
// delete first value
node.values.shift();
// start new counter
node.count = limit-1;
}
node.count++;
node.values.push(val);
node.sum += val;
node.avg = node.sum/node.count;
node.max = node.maxA;
node.min = node.minA;
// remember entered values for the next loop
nodes[key] = node;

// get the result depending on
// selected total_types
subres = [];
for (var t = 0; t < types.length; t++) {
subres.push(node[types[t]]);
}
result.push(subres);
}
// console.log(JSON.stringify(nodes, null, 4));
return result;
}

More sample usages:

• Running sum with group by column A: =runningTotal(B3:B,1,,A3:A)
• Running max =runningTotal(A3:A,4)

(2022 update: With respect to other solutions and their authors, the other solutions are now outdated, and should no longer be used due to simplicity or performance reasons, unless backwards-compatibility is required)

This is now the best way to do so.:

=SCAN(0, A3:A, LAMBDA(acc,x, acc+x))

This works without any 'fuss' (no copy-pasting formulas when you edit things; this "auto-expands" like an ARRAYFORMULA would). It is a single cell that always just works. Demonstration:

You can also crop the cumulative sum after a certain point: For example, FILTER is a function that works like ARRAYFORMULA that you can use to filter out the blank data (otherwise the cumulative sum will think "number+blank=number", which is actually a very reasonable thing to do).

You could have just specified A3:A9 in the example above to avoid having to filter out blanks.

Of course, maybe you want the cumulative sum to continue forever; that is also reasonable.

Furthermore this is performant; solutions using ARRAYFORMULA and SUMIF will slow down on large datasets because they do quadraticly-scaling amounts of unnecessary work.

• Thank you for this this was incredibly helpful and intuitive. I managed to get rid of trailing values by putting a IF formula. =SCAN(0, A3:A, LAMBDA(acc, x, IF(x = "",, acc+x))) Commented Oct 18, 2023 at 0:25