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?

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：

Sadly, since arrayformula is not a true array mapping function -- and failing to be so, sadly, in the key examples of sum and and, the only way to centralize the formula in such scenarios is to use matrix multiplication or, sometimes, query.

For simple summation along the columns, recall that the equivalent matrix multiplication is left-multiply by a corresponding row vector of 1s.

Hence you can do,

=mmult(sequence(1,rows("RangeToBeSummed"),1,0),"RangeToBeSummed")

It works. But you should be automatically alarmed of possible issue with computational efficiency whenever you use mmult to find aggregate statistics. Here, we are introducing the creation of an array, though only a vector in this case; we are introducing multiply-by-1s to the original task.

Neither of the above, altogether double or triple the calculation time at worst, in this case should be too demanding. So an eyeballed estimate is that mmult should perform similarly as "spreaded", or iterated formulas for column-wise sums.

Also the resultant range inherits the formatting of your original range, such as in your example sheet, which is nice. (Query wouldn't do that.)

I'll end with this general tip for centralizing formulas for aggregate statistics of sort:

• try to use mmult, facilitated by filter, or sequence

• think about computational efficiency

• when desperate, consider query