Forum Discussion
LET and Nested Dynamic Array FILTER Function
I'm looking for a way to reference a FILTERED dynamic array column, nested inside the LET functions, e.g.: LET(A,FILTER(A:F,(A:A="X")*(B:B<=DATEVALUE("12/31/2021"))), A_____) How would I reference ...
If you filter an array, it is possible to select columns from the result using INDEX
= LET(
A, FILTER(array,(colA="X")*(colB<=DATEVALUE("31/12/2021"))),
k, SEQUENCE(ROWS(A)),
A₀, INDEX(A,k,{2,3,5}),
A₀)The resulting object is an array and not a range reference so you cannot apply COUNTIFS to it, but any array calculation is possible. The local variable, here A₀, can be used for on-going calculation within the LET function, or the LET itself may be nested within further formulae.
If you require the result to be in the form of a range reference, you could place the formula on a distant sheet and address the spilt helper range.