SOLVED

# How to get Running and Reducing Total at Once

Copper Contributor

# 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 so that result will spill automatically.

8 Replies

# Re: How to get Running and Reducing Total at Once

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

# Re: How to get Running and Reducing Total at Once

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

# Re: How to get Running and Reducing Total at Once

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

# Re: How to get Running and Reducing Total at Once

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

# Re: How to get Running and Reducing Total at Once

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

# Re: How to get Running and Reducing Total at Once

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

# Re: How to get Running and Reducing Total at Once

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

# Re: How to get Running and Reducing Total at Once

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

# Re: How to get Running and Reducing Total at Once

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