Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

How to get Running and Reducing Total at Once

Copper Contributor



In this given snap, I used the increasing/decreasing range inside SUMIFS to get the result I wanted. I need to drag down the range below, but how can we perform using spillover functionality so that result will spill automatically.

8 Replies

@sbmln Consider using a structured Excel table with structured references. enter the formula in the first cell of the RT column. Press enter and it will copy itself down the entire column (i.e. not equal to spilling!) automatically. And when the table grows longer, the formula will 'grow' with it. Example in the attached file.


Screenshot 2023-12-14 at 09.13.39.png

Excellent !
But how to get the result without using table, running total and reducing total both.

@sbmln I'll gladly leave that to those who are much quicker than me constructing the required "all-in-one" dynamic array formula for this. Not really able to spend too much time on myself right now. Sorry.

Hi @sbmln 



  range,      B3:C11,
  array,      SORT( HSTACK( SEQUENCE(ROWS(range)), range), 2),
  products,   SORT( UNIQUE( CHOOSECOLS(array, 2) ) ),
  FilterProd, LAMBDA(arr,prod,
    CHOOSECOLS( FILTER(arr, CHOOSECOLS(arr,2) = prod), 3)
  Accum,      LAMBDA(init,v, init + v),
  Cumulate,   LAMBDA(arr,
      RunTotal,   SCAN(,arr, Accum),
      Total,      TAKE(RunTotal, -1),
      ReducTotal, SCAN(Total,-DROP(arr,-1), Accum),
      IF(ROWS(arr) = 1,
        HSTACK(arr, arr),
        HSTACK(RunTotal, VSTACK(Total,ReducTotal))
  StackCumul, LAMBDA(init,product,
    VSTACK(init, Cumulate( FilterProd(array,product) ))
  RunTotals,  DROP( REDUCE(0,products, StackCumul), 1),
    CHOOSECOLS(array, 1)




There's always one to ask for something :)
FYI there was a potential #CALC! issue in what I posted the other day

Nice to see your solution, although I also got a solution with MAP function.
For Running total
For Reducing total
best response confirmed by sbmln (Copper Contributor)



Interesting. This doesn't seem to scale very well though


In attached file  a range of 50k rows with 51 Products. In D2 your MAP formula for Running Total, in K2 what I suggested the other day. I let you try and see if MAP remains the best option for your scenario...

1 best response

Accepted Solutions
best response confirmed by sbmln (Copper Contributor)


View solution in original post