SOLVED

How to get Running and Reducing Total at Once

Copper Contributor

sbmln_0-1702539148450.png

 

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 

 

Sample.png

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

 

 

@sbmln 

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
=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)))
best response confirmed by sbmln (Copper Contributor)
Solution
=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)))

@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...

1 best response

Accepted Solutions
best response confirmed by sbmln (Copper Contributor)
Solution
=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)))

View solution in original post