How to reference spill range in Let() function

[rkaczano]

I have a range based on a Filter function which spills into AP3:AR46

I have a column that calculates a cumulative sum function as follows on the data in column AQ:

=LET(s,AQ3:AQ46,k,SEQUENCE(ROWS(s)),SUBTOTAL(9,OFFSET(s,0,0,k)))

I am trying to get around using the fixed range reference of AQ3:AQ46 and replacing this with AQ3# does not seem to work.

Any idea of how to make this work?

Thanks

 

[Fluff]

How about

=SCAN(0,index(AP3#,,2),LAMBDA(a,b,a+b))

 

[rkaczano]

Yes that works - thank-you.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[smozgur]

Yes that works - thank-you.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.