Search code examples
excellambdaexcel-formula

Calculating a running count without using OFFSET

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?

enter image description here

Solution

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