How to integrate this sum to ArrayFormula?

Question

I would like the cell to always sum all cells that are superior. When I drag, the formula is copied correctly:

**CelJ3** =SE(I3="";"";SUM($I$2:I3))
**CelJ4** =SE(I4="";"";SUM($I$2:I4))
.
.
.
.
**CelJ100** =SE(I100="";"";SUM($I$2:I100))

But how do you put this formula in the ArrayFormula so that every column contains the formula?

player0 · Accepted Answer · 2021-01-04T21:56:51.707

1

delete everything you have in J column and use this in J3:

=ARRAYFORMULA(IF(I3:I="",,SUMIF(ROW(I3:I), "<="&ROW(I3:I), I3:I)))

faster formula:

=ARRAYFORMULA(MMULT(
 TRANSPOSE((ROW(INDIRECT("I3:I"&MAX(ROW(I:I)*(I:I<>""))))<=
 TRANSPOSE( ROW(INDIRECT("I3:I"&MAX(ROW(I:I)*(I:I<>""))))))*
                INDIRECT("I3:I"&MAX(ROW(I:I)*(I:I<>"")))),
          QUERY(INDIRECT("I3:I"&MAX(ROW(I:I)*(I:I<>""))),  
          "select 1 label 1''")))

edited Jan 04 '21 at 21:56

answered Jan 04 '21 at 20:59

player0

124,011
12
67
124

hi, thanks for your help, at first it worked, but the result is unfeasible, it returns the value correctly, but when I add a new data, it takes several minutes to provide the result – Chris Patterson Fernandes Jan 04 '21 at 21:26
@ChrisPattersonFernandes can you share a copy of your sheet? – player0 Jan 04 '21 at 21:50
@ChrisPattersonFernandes answer updated... – player0 Jan 04 '21 at 21:57
1

thanks again, the quick formula was perfect – Chris Patterson Fernandes Jan 04 '21 at 22:25

How to integrate this sum to ArrayFormula?

1 Answers1

faster formula: