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.

=MAP($A$2:$A$10,B2:B10,LAMBDA(a,b,SUMIFS(B10:b,A10:a,a)))=MAP($A$2:$A$10,B2:B10,LAMBDA(a,b,SUMIFS(B2:b,A2:a,a)))

How to get Running and Reducing Total at Once

sbmln
Copper Contributor
Dec 19, 2023
=MAP($A$2:$A$10,B2:B10,LAMBDA(a,b,SUMIFS(B10:b,A10:a,a)))

=MAP($A$2:$A$10,B2:B10,LAMBDA(a,b,SUMIFS(B2:b,A2:a,a)))
Lorenzo
Silver Contributor
Dec 19, 2023
sbmln
There's always one to ask for something 🙂
FYI there was a potential #CALC! issue in what I posted the other day
- sbmln
  Copper Contributor
  Dec 19, 2023
  Nice to see your solution, although I also got a solution with MAP function.
  For Running total
  =MAP($A$2:$A$10,B2:B10,LAMBDA(a,b,SUMIFS(B2:b,A2:a,a)))
  For Reducing total
  =MAP($A$2:$A$10,B2:B10,LAMBDA(a,b,SUMIFS(B10:b,A10:a,a)))
  - Lorenzo
    Silver Contributor
    Dec 19, 2023
    sbmln
    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...
    LargeRunTotal365_sbmln.xlsx737 KB

Silver Contributor

Dec 14, 2023

=LET(
  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,
    LET(
      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),
  SORTBY(
    RunTotals,
    CHOOSECOLS(array, 1)
  )
)

RunTotal365_sbmln.xlsx11 KB

Riny_van_Eekelen
Platinum Contributor
Dec 14, 2023
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.

sbmin.xlsx10 KB
- sbmln
  Copper Contributor
  Dec 14, 2023
  Excellent !
  But how to get the result without using table, running total and reducing total both.
  - Riny_van_Eekelen
    Platinum Contributor
    Dec 14, 2023
    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.

Forum Discussion

How to get Running and Reducing Total at Once

Resources