Search code examples
arraysgoogle-sheetssum

Accumulated value sum with Arrayformula

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?
Make a copy of the example sheet.

enter image description here

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

enter image description here

Solution

  • A simple way to calculate cumulative sum:

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