CUMULATIVESUM

=CUMULATIVESUM(array)

Description

CUMULATIVESUM returns a running total or cumulative sum of the values in array, for each column separately.

This function provides another method for creating a running balance that does not break when you insert or delete rows within the array.

The commonly recommended method for a cumulative sum is to use the SCAN function like this:

=SCAN(0, array, LAMBDA(acc,v,SUM(acc,v)) )

However, I found that it occasionally would not update automatically if the array was an expression instead of a direct reference to a range (requiring an extra forced recalculation of the worksheet).

CUMULATIVESUM is likely less efficient than using SCAN, because it performs redundant summations. However, it updates as expected when the array is a result of an expression, and it can also work with arrays of more than one column. In the example below, you can see that CUMULATIVESUM returns a running total for each separate column in the original array.

Lambda Formula

This code for using CUMULATIVESUM in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

/**
* Return a running total (cumulative sum) of values in an array
* for each column separately.
*/
CUMULATIVESUM = LAMBDA(array,
LET(doc,"https://www.vertex42.com/lambda/cumulativesum.html",
    MAKEARRAY(ROWS(array),COLUMNS(array),LAMBDA(r,c,
        SUM(INDEX(array,SEQUENCE(r),c))
    ))
));

Name: CUMULATIVESUM
Description: Return a running total or cumulative sum of values in an array
Arguments: char, within_text
Function: 
=LET(doc,"https://www.vertex42.com/lambda/cumulativesum.html",
MAKEARRAY(ROWS(array),COLUMNS(array),
LAMBDA(r,c,SUM(CHOOSEROWS(CHOOSECOLS(array,c),SEQUENCE(r,1))) )
))