Google Spreadsheet: how to automatically fill column by aggregate function

1
votes

Let's the spreadsheet has two columns. J contains values and K contains sum of all "previous" and current values in J.
I set K0 to =SUM(J$0:J0) and drag it every time new row is added to J, so
K1 becomes =SUM(J$0:J1),
K2 becomes =SUM(J$0:J2), etc.
It works well and I am just wondering whether I can avoid doing it manually by replacing K by some array formula?
I tried something like
=arrayformula(if(J:J,SUM(J$0:J),"")) - it returns same value for all the rows = sum of all the values
and I tried "indirect":
=arrayformula(if(J:J,SUM(J$0:indirect("J"&ROW(J0:J))),"")) - it returns same value for all the rows = first value - it seems that indirect calculated only once at the beginning

1
google-sheets

1 Answers

2
votes

Thanks to Kishan for magic answer https://productforums.google.com/forum/#!msg/docs/y_giF0ziQr8/ev213r72OKQJ

Try the following formula in Cell K1: =ArrayFormula(if(J:J="";"";mmult((row(J:J)>=transpose(row(J:J)))*transpose(J:J);row(J:J)^0)))