Forum Discussion

sbmln's avatar
sbmln
Copper Contributor
Dec 14, 2023
Solved

How to get Running and Reducing Total at Once

  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...
excel
Formulas and Functions
  • sbmln's avatar
    sbmln
    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's avatar
Lorenzo
Silver Contributor
Dec 14, 2023

Hi sbmln 

 

=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)
  )
)

 

 

Resources