How to perform calculation with cumulative sum using ARRAYFORMULA

Question

Is it possible to perform an arbitrary calculation (eg. A2*B2) on a set of rows and obtain the cumulative sum along the way using ARRAYFORMULA? For example, in the following sheet we have numbers (column A), multipliers (column B), the result of multiplying them (column C), and a cumulative tally (column D):

  | A       B           C       D           E                F
-------------------------------------------------------------------------------
1 | number  multiplier  result  cumulative  array formula    array formula sum?
2 | 3       4           12      12          12  
3 | 2       4           8       20          8   
4 | 10      1           10      30          10  
5 | 7       9           63      93          63  

I can use ARRAYFORMULA in cell E2 (specifically, ARRAYFORMULA(A2:A5*B2:B5)) to do the multiplication. Is it possible to use ARRAYFORMULA (or alternative tool) in cell F2 to show the cumulative total?

Answer 1

Calculate the cumulative sum with the SCAN and LAMBDA functions:

=SCAN(0, F5:F, LAMBDA(accumulated_value, cell_value, accumulated_value + cell_value))

This will run faster as it runs with linear complexity (O(N)) compared to the ARRAYFORMULA solution, which runs in quadratic time (O(N**2)).

Where:

• 0 is the initial value of the cumulative sum
• F5:F is the range to sum over
• LAMBDA(accumulated_value, cell_value, accumulated_value + cell_value)) is the function that calculates the sum at each cell

Sample File