I have a row of values B2:F2 I want to SUM like i did in B3:F3 but with the use of Arrayformula.

formulas in row 3 with locked $B column:

Month	Jan	Feb	Mar	Apr	May
Value	15,106	15,559	10,875	21,679	18,118
Simple Cell formula	=SUM($B2:B2)	=SUM($B2:C2)	=SUM($B2:D2)	=SUM($B2:E2)	=SUM($B2:F2)

---

Progress: I tried this formula but it outputs the SUM of the entire range B2:F2 at once in the entire range B4:F4.

=ArrayFormula(IF(B2:F2="",,SUM(B2:$F2)))

Month	Jan	Feb	Mar	Apr	May
Value	15,106	15,559	10,875	21,679	18,118
Progress	=ArrayFormula(IF(B2:F2="",,SUM(B2:$F2)))	81,336	81,336	81,336	81,336

---

What is the best formula to get the same result in B3:F3 but using Arrayformula?

Update

When tring to roll forward i discoverd the case when the value row cell are empty, like this in column J, if possible address this case in the answer

CodePudding user response：

standard transposed running total fx will do:

=INDEX(TRANSPOSE(MMULT(TRANSPOSE((SEQUENCE(5)<=SEQUENCE(1, 5))*
 FLATTEN(B2:F2)), SEQUENCE(5, 1, 1, 0))))

---

fully dynamic and maximally lightweight:

=INDEX(IF(C2:2="",,TRANSPOSE(MMULT(TRANSPOSE((
 SEQUENCE(   MAX(COLUMN(C2:2)*(C2:2<>""))-COLUMN(C2) 1)<=
 SEQUENCE(1, MAX(COLUMN(C2:2)*(C2:2<>""))-COLUMN(C2) 1))*
 FLATTEN(INDIRECT("C2:"&ADDRESS(2, MAX(COLUMN(C2:2)*(C2:2<>"")))))), 
 SEQUENCE(   MAX(COLUMN(C2:2)*(C2:2<>""))-COLUMN(C2) 1, 1, 1, 0)))))

CodePudding user response：

A simple way to calculate cumulative sum:

=ArrayFormula(IF(B2:2="",,SUMIF(COLUMN(B2:2),"<="&COLUMN(B2:2),B2:2)))

CodePudding user response：

=arrayformula(mmult(if(isblank(B2:F2),0,B2:F2),if(column(B2:F2)>=transpose(column(B2:F2)),1,0)))

can produce a running sum in a row vector and can accommodate empty entries in the input range.

If you want to auto detect the number of columns in the input range, you can

1. replace B2:F2 with array_constrain(B2:2,1,max(arrayformula(if(isblank(B2:2),,column(B2:2))))-1) and
2. replace column(B2:F2) with array_constrain(column(B2:2),1,max(arrayformula(if(isblank(B2:2),,column(B2:2))))-1)

which is to say, cut the range leaving the number of rows that is the max column index of occupied cells in our range; minus 1 because we started with column 2.

(Also, as long as there is one arrayformula wrapping the whole formula, you can omit them in the nested inputs, as long as you preserve the () brackets.)

Nonetheless, there would be a (computational) efficiency concern.

In order to centralize the formula, in the above solution, we first created a filter for each desired entry in our running sum vector, 1,0,0,... for 1st entry, 1,1,0,... for 2nd entry, 1,1,1,0,... for 3rd, etc. And then, effectly, we apply a sum(filter(...)) via multiply by 1 or 0 using mmult. The array creation costs extra. The multiplication costs extra. And compared to iterated formulas that mutates cell by cell, we are not saving the multiply by 0 parts.

It may not end up being more than double or triple the runtime compared to iterated formulas. And you can experiment case by case. Small scale application is always fine. But for larger datasets, computational efficiency is something to keep in mind whenever we introduce extra computational steps, and potentially squaring the original amount when using mmult solutions.