CODEWITHSUNDEEP
arraysgoogle-sheetssumarray-formulascumulative-sum
greeny
greeny

Reputation: 445

Rolling sum with array formula in Google Sheets

I have the dataset below. Col1 is given data and Col2 is the rolling sum of the previous 5 rows of Col1 (inclusive).

Date      Col1  Col2
01/04/20  2     2
02/04/20  1     3 
03/04/20  4     7
04/04/20        7
05/04/20        7
06/04/20  5     10
07/04/20  2     11
08/04/20        7
09/04/20        7
10/04/20  1     8
11/04/20        3
12/04/20        1
13/04/20        1
14/04/20        1
15/04/20  1     1

Is there a way to use arrayformula to do this rather than inputting a sum formula into every cell in Col2 going down?

I had a similar question using count and was given this solution:

=ArrayFormula(filter(countifs(B2:B,">0",row(B2:B),"<="&row(B2:B),row(B2:B),">"&row(B2:B)-5),A2:A<>""))

Can I amend this to work for sum?

Upvotes: 1

Views: 1472

Answers (3)

Tom Sharpe
Tom Sharpe

Reputation: 34180

You can also do it by the difference of two rolling sums if you wish:

=ArrayFormula(filter(sumif(row(B2:B),"<="&row(B2:B),B2:B)-sumif(row(B2:B),"<="&row(B2:B)-5,B2:B),A2:A<>""))

enter image description here

Upvotes: 3

Iamblichus
Iamblichus

Reputation: 19309

You can accomplish this via a custom function created in Google Apps Script. To achieve this, follow these steps:

  • In your spreadsheet, select Tools > Script editor to open a script bound to your file.
  • Copy this function in the script editor, and save the project:
function SUMROWS(input, number) {
  var output = [];
  var sum;
  for (var row = 0; row < input.length; row++) {
    sum = 0;
    for (var i = number - 1; i >= 0; i--) {
      if (row - i >= 0) {
        sum = sum + Number(input[row - i]);
      }
    }
    output.push(sum);
  }
  return output;
}
  • Now, if you go back to your spreadsheet, you can use this function just as you would do with any other function. You just have to provide the appropriate range (in this case it would be B2:B16) and the number of previous rows to sum (in this case, 5) as parameters, as you can see here:

enter image description here

Reference:

Upvotes: 2

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(SUMIF(ROW(B2:B), "<="&ROW(B2:B), B2:B))

Upvotes: 0

Related Questions