ARRAYFORMULA to sum previous row cell

3
votes

I have a data sheet like this.

Column--    |C1|C2|C3|Total|
=========================
Row--1      | 0| 0| 0|  100| (I will have some pre calculated value for total column which is not sum of the columns in this row)

Row--2      |10|20|30|  160| (sum of previous row =100+current row (10+20+30) = 160)

Row--3      | 1| 2| 3|  166|

Row--4      | 4| 5| 6|  181|

Today I am using a copy paste formula for each row. However, I want an array formula for SUM column so that the formula will automatically work when I add new rows.

What I want is, SUM column will contain sum of current row A through D values and previous row SUM value.

So in a google spread sheet I'll initially have 20 rows only. Based on the transactions I'll add rows and so I want an array formula so that my formula will work even after adding new rows.

Added an example sheet

https://docs.google.com/spreadsheets/d/1wlWqdFwgv90s50iP-bXXBHciyualohj610qFiSatcmQ/edit?usp=sharing

3
google-sheetsarray-formulas
give a proper screenshot of your data and the expected results; – Marcel
@AHC I've edited the question, is it understandable? – Venu Madhav
the formula will work for row 2. so I want in row 3, I've to drag the formula or copy paste the formula. What if I've 2000+ rows and I can't keep dragging for those many rows. More over I'll have same scenarios for some more set of columns in the same rows. so I am looking for an array formula – Venu Madhav
This is fine. But the sheet is complicated and I want to simplify the formule in the sheet. So I am looking for an array formula. Is there a way? – Venu Madhav
None of the suggested comments or answers worked. Is there any solution? – Venu Madhav

3 Answers

1
votes

Another method for an array cumulative sum:

=ArrayFormula(SUMIF(IF(COLUMN(A1:D1),ROW(A2:A)),"<="&ROW(A2:A),A2:D))

0
votes

I think I would go for a solution involving two formulas:

in E1, a formula that takes care of the summing per row;

=ArrayFormula(mmult(filter(A1:D,len(A1:A)), transpose(column(A1:D1)^0)))

and then in F1: the 'cumulation' of previous rows:

=ArrayFormula(SUMIF(ROW(A1:A1064),"<="&ROW(A1:A1064),E1:E1064))

(change the range to suit)

See also: example sheet 1

If you really need a solution in one single formula, you can try:

=ArrayFormula(if(len(A1:A1064), if(row(A1:A1064)=1, mmult(A1:D1, transpose(column(A1:D1)^0)),if(A1:A1064,mmult(transpose(if(transpose(row(A1:D1064))>=row(A1:D1064),A1:A1064+B1:B1064+C1:C1064+D1:D1064, 0)),row(A1:A1064)^0),)),))

See also example sheet 2

-1
votes

Try this example sheet
simply put this in the first row of E:

=SUM(A2:D2)

and then:

=IF((COUNTA(A3:D3)>1),SUM(A3:D3)+E2,"")

and drag it down to 2000+ rows. It is done for your in the example sheet. If you add new data in the rows, you will get what you want.