Calculating a running count without using OFFSET

Question

I'm trying to find an alternative to my formula that calculates the running count of multiple values in a range.

It's returning the correct results, but the issue is it uses the volatile function OFFSET(), which I would like to avoid.

Here is the formula:

=SCAN(0,A2:A13,
LAMBDA(a,b,
IF(OFFSET(b,-1,0)=b,
a+1,1)
)
)

I have tried using INDEX() and ROW(), but I cannot replicate what OFFSET() does.

How can I alter the formula?

Answer 1

Running Count

• The trick is to compare the column with a version of the column shifted one row down. I'm using VSTACK with "" to add a new (empty) first row and DROP with -1 to get rid of the last row.

=LET(Data,A2:A13,
SCAN(0,Data=DROP(VSTACK("",Data),-1),
    LAMBDA(a,b,IF(b,a+1,1))))

Answer 2

In short, you'd need a way to iterate an array of truthy/falsey values that would indicate the Previous value == Current value. If you are working with cell-references this is easy:

=SCAN(0,A1:A12=A2:A13,LAMBDA(a,b,IF(b,a+1,1)))

You'll end up with a running count of consecutive values that would reset accordingly.

For good measure; you can avoid the use of INDIRECT() when using SCAN(). Refer to each element's respective row/column if need be as per this example.