Arrayformula Running Total with multiple columns

Question

I've got the following task to solve, but I can't wrap my head around it.

There are a couple of numbers for different years. I would like to have a running total sum of all columns (= range C:I) in column J. I can do that with a formula in every cell of J - however I need to achieve it with a single arrayformula in J2.

What i figured after a lot of research are 2 steps:

replace empty cells with 0s, since arrayformulas obviously have some problems with empty cells
make a sum of each row

For step 1 the formula is: =ARRAYFORMULA(IF(ISBLANK(C2:I15),0,C2:I15)) which gives me back a temporary array like:

As this is a 15x7 array, I need something like a 7x1 array to multiply with in order to get a 15x1 array.

That formula would be: =ARRAYFORMULA(TRANSPOSE(COLUMN(C1:I1)^0))

So in the end my formula so far look like: =ARRAYFORMULA(MMULT(IF(ISBLANK(C2:I15),0,C2:I15),TRANSPOSE(COLUMN(C1:I1)^0)))

And this results in a 15x1 array that gives me the sum per row, but not a total running sum of all rows.

This is where I am stuck - any help and ideas are greatly appreciated.

EDIT: added a shared version for you to fiddle: https://docs.google.com/spreadsheets/d/1cqNEsWHqBaHdDrMY8x4DUKpEkYprRZ8AibEe7d0knPY/edit?usp=sharing

so you want to perform running total for each column of C:I range and then sum each row into one single column OR you want to sum each row in range C:I and then perform running total to end up with one column ??? — player0
I want to achieve a running total of the range C:I (see first screenshot). So each row the running total should increase. I added a shared sheet. — sangrila B

player0 player0 · Accepted Answer · 2020-01-19T16:42:00

try:

=ARRAYFORMULA(IF(B2:B="";;MMULT(TRANSPOSE((ROW(M2:M)<=TRANSPOSE(ROW(M2:M)))*
 MMULT(C2:I*1; ROW(A1:A7)^0)); SIGN(MMULT(C2:I*1; ROW(A1:A7)^0))^0)))

Arrayformula Running Total with multiple columns

2 Answers