How do i sum up number of consecutive values within the same column?

[#1]

Hi,

I was able to identify consecutive values on a column "Data" (Refer to " sequence roll" column). However i need to generate a column (refer to Expected Result column) to sum up the number of consecutive values.

Any help or advice will be appreciate. Thank you

" sequence roll" column

If( :Item == Lag( :Item, 1 ),
If( Dif( :Data, 1 ) == 1,
Dif( :Data, 1 ), .
), .
)

[#2]

[#3]

Is there a specific reason of dropping out the value from first matching row while still adding it to the calculation?

Here you have 5 consecutive values but first row is set as missing.

[#4 · Accepted solution]

I would create two columns, Sequence with formula such as 

As Constant(seq = 0);
If(Dif(:Data) != 1, 
	seq++
);
seq;

and then use that for calculations using Col Number

Col Number(:Sequence, :Sequence)

If you want to make first row missing, you can do that by adding if statement to result

If(Row() == Col Min(Row(), :Sequence),
	.
,
	Col Number(:Sequence, :Sequence)
)

[#5]

It works!

Thank you

[#6]

Hi,

No specific reason. Just that my formula to check sequence roll is depending on the previous row above.