I have the dynamic range A (A#) and I want to calculate the cumulative sum of each "subrange" in B. The result musst be something as shown in column B. In other words I need a dynamic function/ formula which recognises the consecutive numbers in A und computes their total each time.
I only managed to solve this with a helper column C
C1: 1
C2: =IF(A1=A2,C1,C1+1)
C2 is draggable all the way down where you need it
B1: =IF(A1=A2,"",SUMIFS(A1,A1,A1))
B2: =IF(A2=A3,"",SUMIFS($A$1:A2,$C$1:C2,C2,$A$1:A2,A2))
B2 is draggable all the way down where you need it
With Office 365 you can use this LET to create a dynamic array that spills into Column B as you have shown:
=LET( a, A1#,
sa, SCAN( 0, a, LAMBDA(a,b, a + b ) ),
rSeq, SEQUENCE( ROWS( a ) ),
a_1, INDEX( a, rSeq + 1 ),
change, IFERROR( a_1 <> a, 1 ),
sChange, SCAN( 0, change*sa, LAMBDA(a,b, MAX( a, b ) ) ),
IF( change, sChange - IF( rSeq=1, 0, INDEX( sChange, rSeq - 1) ), "" ) )
where A1# is the input array.
