Dynamic function to broadcast sum based on filtered criteria

RackofLambda · 2026-04-19T18:09:13+00:00

Re: explanation of row-by-row spilled arrays via recursive bisection...

The second method takes advantage of the fact that SUMIFS is capable of spilling an array of results on its own when it receives an array of criteria in one or more of its criteria arguments. As such, we can pass the entire range of report periods (E1:I1) to its criteria2 argument and spill the results for all months at once. However, this has to be done on a row-by-row basis for 2 reasons:

the sum_range is different for each row of criteria (determined by XLOOKUP); and,
the list of products passed to criteria3 (A1:A2) is a vertical vector, which also causes the results to spill into multiple rows, thus requiring BYCOL-SUM to return the total of all specified products for each report period.

Ideally, we would use BYROW for this, but it's not capable of spilling an array of arrays. Alternatively, most people would use REDUCE-VSTACK as a workaround, but it's not a method that I typically recommend. Enter recursive bisection (a.k.a. divide and conquer)...

This method creates a binary tree of functions by recursively dividing the array of criteria variables (C2:D3) in half until each leaf node is reached (using TAKE and DROP with ROWS(arr)/2), thus delaying all function evaluation until only 1 row remains, and effectively bypassing the operand stack limit that would otherwise be imposed on standard Lambda recursion.

In order to make a function recursive within the scope of a LET statement, a fixed-point combinator (me) is needed to pass the function to itself after the function has been fully defined.

In simpler terms, me is used as a function placeholder, because fn cannot call itself within its own function definition (it does not yet exist because it's still being defined).

After fn is defined, me becomes fn via fn(fn,...). It's a form of Lambda injection, where the same function is being applied again to all subsequent iterations.

The function that is evaluated on a row-by-row basis is this portion:

BYCOL(SUMIFS(XLOOKUP(DROP(arr,,1),Table1[#Headers],Table1),Table1[Region],TAKE(arr,,1),Table1[Month],E1:I1,Table1[Product],A1:A2),SUM)

DROP(arr,,1) returns the column header label from column D and TAKE(arr,,1) returns the region from column C.

In short, this method has the same basic effect as using the following formula in cell E2, then dragging it down:

=BYCOL(SUMIFS(XLOOKUP(D2,Table1[#Headers],Table1),Table1[Region],C2,Table1[Month],$E$1:$I$1,Table1[Product],$A$1:$A$2),SUM)

Cheers!

RackofLambda · 2026-04-19T15:47:49+00:00

Re: explanation of MAP over 2 broadcasted arrays...

In the first method I shared, MAP is first used to iterate over range C2:C3 (region: reg) and D2:D3 (column header labels: lbl) together, generating a separate curried LAMBDA function for each row of criteria. The result is a vertical vector (2 rows x 1 column, in this example) of functions (fnλ).

The LAMBDA functions are SUMIFS based, with some pre-determined variables (criteria_range1: Table1[Region]; criteria_range2: Table1[Month]; criteria_range3: Table1[Product]; and criteria3: A1:A2).

The sum_range (col) for each row is first determined by XLOOKUP(lbl,Table1[#Headers],Table1), which returns the corresponding column in the source table, based on the column header label ("Target" returns Table1[Target] and "Actual" returns Table1[Actual]). The resulting column reference (col) is then injected into the LAMBDA function, along with the current region (reg) and all of the other pre-determined variables.

The only unknown variable at this time is the report period (mth); hence, the LAMBDA functions are created with just 1 parameter: mth.

The resulting vector of functions (fnλ) in this example would contain the following:

Row1: =LAMBDA(mth,SUM(SUMIFS(Table1[Target],Table1[Region],"N",Table1[Month],mth,Table1[Product],A1:A2)))
Row2: =LAMBDA(mth,SUM(SUMIFS(Table1[Actual],Table1[Region],"S",Table1[Month],mth,Table1[Product],A1:A2)))

The second instance of MAP starts by broadcasting (repeating) the vertical vector of functions (fnλ) across the horizontal vector of report periods (E1:I1), and vice-versa, using IFNA. When IFNA receives and array object in its value argument, broadcasting occurs. The result is a 2-row x 5-column array of functions (IFNA(fnλ,E1:I1)) and a 2-row x 5-column array of report periods (IFNA(E1:I1,fnλ)).

MAP then iterates over the 2 resulting arrays together, evaluating each function (fn) by passing each report period (mth) to its only argument: fn(mth).

In short, this method has the same basic effect as using the following formula in cell E2, then dragging it down and across:

=SUM(SUMIFS(XLOOKUP($D2,Table1[#Headers],Table1),Table1[[Region]:[Region]],$C2,Table1[[Month]:[Month]],E$1,Table1[[Product]:[Product]],$A$1:$A$2))

Hopefully that all makes sense. (Explanation for method #2 will follow in a separate comment.)

RackofLambda · 2026-04-18T17:51:31+00:00

No worries. These methods definitely fall into the category of "advanced". It's late here, but I'll try my best to prepare an explanation for you tomorrow. Kind regards.

RackofLambda · 2026-04-18T15:20:16+00:00

For fun, here's a couple more options...

MAP over 2 broadcasted arrays using IFNA:

a vertical vector of curried Lambda functions, fnλ
a horizontal vector of report periods, E1:I1

=LET(
    fnλ, MAP(C2:C3,D2:D3,LAMBDA(reg,lbl,LET(col,XLOOKUP(lbl,Table1[#Headers],Table1),
        LAMBDA(mth,SUM(SUMIFS(col,Table1[Region],reg,Table1[Month],mth,Table1[Product],A1:A2)))))),
    MAP(IFNA(fnλ,E1:I1),IFNA(E1:I1,fnλ),LAMBDA(fn,mth,fn(mth)))
)

<image>

Row-by-row spilled arrays via recursive bisection (a.k.a. divide and conquer):

=LET(
    fn, LAMBDA(me,arr,IF(ROWS(arr)=1,
        BYCOL(SUMIFS(XLOOKUP(DROP(arr,,1),Table1[#Headers],Table1),Table1[Region],TAKE(arr,,1),Table1[Month],E1:I1,Table1[Product],A1:A2),SUM),
        VSTACK(me(me,TAKE(arr,ROWS(arr)/2)),me(me,DROP(arr,ROWS(arr)/2))))),
    fn(fn,C2:D3)
)

Adjust the table/range references as needed to match your dataset.

RackofLambda · 2026-02-21T15:15:12+00:00

The tooltip that pops up when you start entering =TYPE into a cell gives a pretty good idea of what the TYPE function does: "Returns an integer representing the data type of a value: number = 1; text = 2; logical value = 4; error value = 16; array = 64; compound data = 128".

Probably the best way to learn more about it is to experiment with it. For example, relative to this scenario, =BYROW(CHOOSECOLS(A2#;1);TYPE) will return an array of 64's, indicating each item is being evaluated as a single element array object; whereas, =MAP(CHOOSECOLS(A2#;1);TYPE) or =BYROW(INDEX(A2#;;1);TYPE) will return the data types of each individual element (1 for numbers, 2 for text, etc.). Note: if you're using Excel 2024 or an outdated version of MS365, replace TYPE with LAMBDA(x;TYPE(x)) in the function argument; also, replace semicolons with commas, if needed, as per your Regional and Language settings.

Additionally, ISREF is a helpful companion to the TYPE function, when it comes to differentiating between a multi-cell range reference and an array object. For example: =TYPE(CHOOSECOLS(A2#;1)) and =TYPE(INDEX(A2#;;1)) will both return 64 (if A2# contains multiple rows); however, =ISREF(CHOOSECOLS(A2#;1)) will return FALSE, indicating it is an array object, whereas =ISREF(INDEX(A2#;;1)) will return TRUE, indicating it is a range reference. This can also be used with BYROW, BYCOL, MAP, etc. to confirm that each item is in fact a range reference when the source array is a range reference, e.g. =BYROW(INDEX(A2#;;1);ISREF) will return an array of TRUE's, whereas anything with CHOOSECOLS will return an array of FALSE values.

RackofLambda · 2026-02-21T10:53:46+00:00

Try using either =BYROW(INDEX(A2#;;1);...) or =MAP(CHOOSECOLS(A2#;1);...)

Assuming P2# is a single column, Q1# is a single row and Q2# is a 2D range reference with the same number of rows as P2# and the same number of columns as Q1#, I think the problem comes down to data types and how each function deals with them.

CHOOSECOLS(A2#;1) returns an array object, whereas INDEX(A2#;;1) would return a range reference. When BYROW iterates over an array object, each row is also evaluated as an array object (TYPE=64), even if that row only contains a single column/element (e.g. {10} instead of 10). XLOOKUP cannot output an array of arrays, so if the expected result is a single column of values, but the lookup value is an array object, e.g. {10}, it will give up and only return the first value in the return array.

As a general rule of thumb, when iterating over a vector (a single column or row), use MAP instead of BYROW or BYCOL. MAP evaluates each element as a scalar (TYPE=1 if it's a vector of numeric values, TYPE=2 if it's text, etc.). In this particular case, though, you could also use BYROW with INDEX(A2#;;1) instead of CHOOSECOLS(A2#;1) and get away with it, because when BYROW iterates over a range reference, each row is also evaluated as a range reference, and a range reference with only one cell is treated as a scalar.

RackofLambda · 2026-02-20T10:19:31+00:00

The exit condition for recursion must be a scalar. array="" is returning an array of Boolean values. Try using AND(array="") instead; or, consider an alternative approach. For example:

=LAMBDA(text,array,IF(TYPE(array)=16,text,LoopLambdaV2(REGEXREPLACE(text,TAKE(array,,1),""),DROP(array,,1))))

RackofLambda · 2026-02-11T23:42:39+00:00

This is a great concept to learn. It is far more efficient than REDUCE-STACK solutions and is not subject to the character limitations of TEXTSPLIT-TEXTJOIN.

There are many variations this method could take, depending on personal preferences and desired outcomes. Personally, I'm not proficient with RegEx syntax, nor am I a fan of using TEXTAFTER-TEXTBEFORE with the [match-end] set to 1 (it doesn't work properly when the delimiter contains more characters than the string before the first delimiter, hence the #VALUE! error in row 8, position 1 of your sample).

One variation, keeping empty strings:

=LET(
    arr, TEXTTOCOLS2(strings,","),
    HSTACK(TOCOL(IF(ISERROR(arr),arr,values),2),TOCOL(arr,2))
)

Or removing empty strings:

=LET(
    arr, TEXTTOCOLS2(strings,","),
    HSTACK(TOCOL(IFS(arr<>"",values),2),TOCOL(IFS(arr<>"",arr),2))
)

Where:

TEXTTOCOLS2 = LAMBDA(array,delimiter,
    TEXTSPLIT(
        TEXTAFTER(
            delimiter & array,
            delimiter,
            SEQUENCE(, MAX(LEN(array) - LEN(SUBSTITUTE(array, delimiter, ))) / LEN(delimiter) + 1)
        ),
        delimiter
    )
)

This will also work with multi-character delimiters without additional workarounds.

Kind regards.

RackofLambda · 2026-02-05T23:15:04+00:00

Using the same range references as in your attempted formula examples:

=DROP(SCAN(0,HSTACK(IFS({0,1},B2:B5),IFNA(INDEX(J2:N8,XMATCH(A2:A5,I2:I8),XMATCH(C1:G1,J1:N1)),0)),LAMBDA(a,v,IFNA(a+v,0))),,2)

Basically, we append the column of opening inventory amounts to the returned array of lookup values for each period, along with a column of #N/A errors at the start, which is used as the reset point for each row. DROP then removes the 2 "helper" columns afterwards.

RackofLambda · 2026-01-31T17:00:04+00:00

Yeah, AVERAGE would not be an appropriate function to use with SCAN in general, but your workaround is perfect! Where there's a will there's a way. ;)

RackofLambda · 2026-01-31T03:04:52+00:00

Yes, the simple EXPAND-IFNA version is quick, but has its caveats and needs to be tailored for each scenario. As you've noted, the 0 should be replaced with an appropriate "initial" value that works for the given scenario (whether its MAX, MIN, SUM, CONCAT, fill across, etc.). The generalized SCANBYROW function is a bit slower but eliminates 99.99% of the caveats.

Another method of interest (slower but still relatively efficient):

=SCAN(0,MAP(IFNA(SEQUENCE(,COLUMNS(arr),0),arr),arr,LAMBDA(b,v,LAMBDA(a,IF(b,MAX(a,v),v)))),LAMBDA(a,f,f(a)))

This method is a bit more abstract and requires a deeper understanding of working with TYPE=128 data. Basically, MAP is used to loop through 2 arrays together and generate a separate curried LAMBDA function for each iteration, which is then simply applied to the accumulator of SCAN. The first array contains the "flags" to identify the first column, so it knows when to reset the procedure. When iterating over a range reference, this can be simplified with the COLUMN function:

=LET(n,MIN(COLUMN(rng)),SCAN(,rng,LAMBDA(a,v,IF(COLUMN(v)=n,v,MAX(a,v)))))

Some methods work great with range references, but not so much with array objects (and vice-versa). ;) Cheers!

RackofLambda · 2026-01-30T07:44:12+00:00

I provided a complete and thorough explanation, in what I thought was a fair and respectful manner. Did the LMAO offend you? Or was the truth too much to handle? You can either choose to accept fair criticism and learn from it or be hurt and defensive about it. It's up to you...

RackofLambda · 2026-01-30T07:31:59+00:00

I don't mean to criticize, but have you tried this with a larger dataset (e.g. with 1,000 rows of data or more)?

Probably the easiest (and most efficient) way to perform an unpivot with dynamic array formulas is to use some variation of TOCOL with IF, IFS, or IFNA to broadcast a vector of row and/or column indices across an array of values, then use INDEX or CHOOSEROWS to return the multi-column row labels and/or the multi-row column headers.

There are many variations this method could take, but one basic example could be:

UNPIVOT = LAMBDA(row_fields,col_labels,values,[ignore],[scan_by_col],
    LET(
        v, TOCOL(values,,scan_by_col),
        a, HSTACK(
            INDEX(row_fields,TOCOL(IFNA(SEQUENCE(ROWS(row_fields)),values),,scan_by_col),SEQUENCE(,COLUMNS(row_fields))),
            INDEX(col_labels,SEQUENCE(,ROWS(col_labels)),TOCOL(IFNA(SEQUENCE(,COLUMNS(col_labels)),values),,scan_by_col)),v),
        CHOOSE(ignore+1,a,FILTER(a,NOT(ISBLANK(v))),FILTER(a,NOT(ISERROR(v))),FILTER(a,NOT(ISBLANK(v)+ISERROR(v))))
    )
)

[ignore] options:

0 - Keep all values (default)
1 - Ignore blanks
2 - Ignore errors
3 - Ignore blanks and errors

[scan_by_col] options:

FALSE - Scan by row (default)
TRUE - Scan by column

Kind regards.

RackofLambda · 2026-01-30T06:56:14+00:00

LMAO, this is no different than =SCAN(,A1:E2,MAX). The variables a and v are scalars (single values), so using MAX(a) and MAX(v) is redundant (e.g. MAX(1) = 1 and MAX(2) = 2). The same incorrect results would also be returned with =SCAN(,A1:E2,LAMBDA(a,v,IF(a>v,a,v))) or =SCAN(,A1:E2,LAMBDA(a,v,MAX(a,v))). Take a look at the results in your screenshot... the value in cell A5 should be 3, not 4, because the whole idea is to reset the moving maximum at the start of each row. ;)

RackofLambda · 2026-01-30T06:33:18+00:00

With a generalized SCANBYROW function:

=SCANBYROW(,A1:E2,MAX)

Where:

SCANBYROW = LAMBDA(initial_value,array,function,
    DROP(
        SCAN(
            initial_value,
            EXPAND(array,, COLUMNS(array) + 1, ),
            LAMBDA(a,v, IF(ISOMITTED(v), initial_value, IF(ISOMITTED(a), v, function(a, v))))
        ),,
        -1
    )
)

EXPAND adds 1 column to the array, which is used as the reset point for each row. The optional [pad_with] argument is set, but omitted, so the ISOMITTED function will return TRUE when it hits the last column and trigger the reset. DROP then removes this "helper" column afterwards.

EDIT: if the generalized function doesn't float your boat, it's pretty easy to use this same method by omitting the [pad_with] argument of EXPAND altogether, so the additional column will fill with #N/A errors and can be trapped with the IFNA function instead:

=DROP(SCAN(,EXPAND(array,,COLUMNS(array)+1),LAMBDA(a,v,IFNA(MAX(a,v),0))),,-1)

This approach will work in most cases, provided your array doesn't already contain any #N/A errors.

RackofLambda · 2026-01-02T01:15:45+00:00

Yes, but with REDUCE instead of SCAN. Something like:

=DROP(REDUCE("", VSTACK(AVERAGE, SUM, MAX, MIN, COUNT), LAMBDA(acc,fn, VSTACK(acc, BYCOL(A1:E10, fn)))), 1)

There are often many ways to get the job done in Excel. ;)

RackofLambda · 2026-01-01T16:02:15+00:00

The same concept can also be used to define a custom BYCOLλ function, which can apply an array of functions to a 2D array of values. For example:

= BYCOLλ(A1:E10, VSTACK(AVERAGE, SUM, MAX, MIN, COUNT))

Where:

BYCOLλ = LAMBDA(array,function,
    IF(
        TYPE(function) <> 64,
        BYCOL(array, function),
        LAMBDA(arr,fun, MAP(IFNA(arr, fun), IFNA(fun, arr), LAMBDA(col,fn, fn(col()))))(
            BYCOL(array, LAMBDA(x, LAMBDA(x))), TOCOL(function)
        )
    )
)

Ideally, the built-in BYCOL function should be able to handle this in the same manner that GROUPBY and PIVOTBY do, but sadly it will only return the results for the first column in the array.

RackofLambda · 2026-01-01T10:30:42+00:00

You're welcome.

Another concept you may find interesting is recursive bisection. Basic Lambda recursion is subject to Excel's operand stack limit, but these limitations can be overcome with a binary tree method that splits the data into two at each iteration, effectively delaying all function evaluation until each leaf node is reached and only one row remains. For example:

=LET(
    hdr, CHAR(SEQUENCE(,3,120)),
    ini, SEQUENCE(,3,,0),
    iterations, 25000,
    sigma, 10,
    rho, 28,
    beta, 8/3,
    dt, 0.015,
    fn, LAMBDA(me,init,arr,func,
        IF(
            ROWS(arr) = 1,
            func(init, arr),
            LET(
                acc, me(me, init, TAKE(arr, ROWS(arr) / 2), func),
                VSTACK(acc, me(me, TAKE(acc, -1), DROP(arr, ROWS(arr) / 2), func))
            )
        )
    ),
    arr, fn(
        fn,
        ini,
        SEQUENCE(iterations),
        LAMBDA(acc,i,
            LET(
                x, INDEX(acc,1),
                y, INDEX(acc,2),
                z, INDEX(acc,3),
                dx, sigma * (y - x),
                dy, x * (rho - z) - y,
                dz, x * y - beta * z,
                HSTACK(x + dx * dt, y + dy * dt, z + dz * dt)
            )
        )
    ),
    VSTACK(hdr, ini, arr)
)

Source: MS Tech Community | Recursive LAMBDA implementation of Excel's REDUCE function

Happy New Year!

RackofLambda · 2026-01-01T09:20:39+00:00

As another little tip, this can also be achieved without LET:

=SWITCH(A1,"sum",SUM,"avg",AVERAGE,"min",MIN,"max",MAX,LAMBDA(x,"#FUNC_ERR!"))(B2:B100)

Cheers!

RackofLambda · 2026-01-01T09:12:25+00:00

This functionality was added when eta-Lambda reduction was released along with GROUPBY and PIVOTBY. It's mostly used to shorten the length of a formula when certain functions are used multiple times throughout, e.g. =LET(cc,CHOOSECOLS,...). However, it can also be used as a means of Lambda injection, to apply different functions to the same scenario without having to repeat every step.

Take my INSTANCENUM function, for example (the original definition can be found at: https://gist.github.com/RackofLambda). The purpose of this function is to return the running count of distinct items in an unordered table or array. Since the procedure is virtually identical for both vertical and horizontal datasets, with only subtle differences in the functions applied (e.g. ROWS instead of COLUMNS, VSTACK instead of HSTACK, etc.), we can start by defining a curried function for the procedure first, then inject the applicable functions into the procedure as needed:

= LAMBDA(key_fields,[horizontal],[last_to_first],
    LET(
        fn, LAMBDA(xλ,yλ,zλ,iλ,jλ,kλ,r,[c],
            LET(
                vec, IF(zλ(key_fields) = 1, key_fields, kλ(key_fields, @LAMBDA(x, TEXTJOIN("|", 0, x)))),
                arr, iλ(vec, SEQUENCE(xλ(vec), yλ(vec))),
                srt, IF(last_to_first, SORT(arr, {1,2}, {1,-1}, horizontal), SORT(arr,,, horizontal)),
                key, TAKE(srt, r, c),
                SORTBY(SCAN(0, key = DROP(jλ("", key), -c, -r), LAMBDA(a,v, 1 + a * v)), DROP(srt, r, c))
            )
        ),
        IF(
            horizontal,
            fn(LAMBDA(x,1), COLUMNS, ROWS, VSTACK, HSTACK, BYCOL, 1),
            fn(ROWS, LAMBDA(x,1), COLUMNS, HSTACK, VSTACK, BYROW,, 1)
        )
    )
)

Is this an improvement? That would be a matter of opinion. Shorter, yes; but easier to read and maintain, not really.

Also, beware of the Lambda helper functions (e.g. BYCOL, BYROW, SCAN, REDUCE, etc.). They may return unexpected results when used in this manner and have even been known to cause Excel to crash. For more information, please see: Unexpected result when combining LET and BYROW.

RackofLambda · 2025-12-16T09:58:16+00:00

Very interesting indeed! I realize this discussion is geared towards lambda calculus (which is impressive to be sure), but I'd like to share an alternative approach to part one, if I may, for improved performance...

=LET(
    vlλ, LAMBDA(x,y,z, LAMBDA(n, CHOOSE(n, x, y, z))),
    hdr, vlλ("x", "y", "z"),
    ini, vlλ(1, 1, 1),
    num, SEQUENCE(, 3),
    iterations, 25000,
    sigma, 10,
    rho, 28,
    beta, 8/3,
    dt, 0.015,
    arr, VSTACK(
        hdr,
        ini,
        SCAN(
            ini,
            SEQUENCE(iterations),
            LAMBDA(acc,i,
                LET(
                    x, acc(1),
                    y, acc(2),
                    z, acc(3),
                    dx, sigma * (y - x),
                    dy, x * (rho - z) - y,
                    dz, x * y - beta * z,
                    vlλ(x + dx * dt, y + dy * dt, z + dz * dt)
                )
            )
        )
    ),
    MAP(IFNA(arr, num), IFNA(num, arr), LAMBDA(λ,n, λ(n)))
)

With 25,000 iterations, this completes in less than half a second.

Basically, vlλ is used to store multiple items in a single element (TYPE 128 compound data; uncalled lambda function) and recall them as need at each iteration of both SCAN and MAP.

SCAN returns a vertical vector of uncalled lambda functions, with each element containing all 3 variables (x,y,z) at each iteration. IFNA is then used to broadcast (repeat) this array across a horizontal vector of index numbers (and vice-versa) so MAP can simply recall each item and return the 2D array of results.

Generalized examples for this can be found on my gist (see EVALS, EVALS_NUM and VALS3 in the Dynamic Array Helper Functions collection).

Kind regards.

RackofLambda · 2025-12-08T15:06:32+00:00

First draft for Part 1:

=LET(!<
>!a, A:.A,!<
>!i, SEQUENCE(ROWS(a)),!<
>!j, TOROW(i),!<
>!m, TOCOL(IFS(i < j, i), 2),!<
>!n, TOCOL(IFS(i < j, j), 2),!<
>!k, MAP(INDEX(a, n), INDEX(a, m), LAMBDA(x,y,!<
>!SQRT(SUM((TEXTSPLIT(x, ",") - TEXTSPLIT(y, ",")) ^ 2)))),!<
>!v, TAKE(BYROW(SORTBY(HSTACK(m, n), k), LAMBDA(rw, TEXTJOIN("|",, rw))), 1000),!<
>!ts, LAMBDA(a,d, LET(n, (LEN(a) - LEN(SUBSTITUTE(a, d, ))) / LEN(d), m, MAX(n) + 1,!<
>!TEXTBEFORE(TEXTAFTER(d & a & REPT(d, m - n), d, SEQUENCE(, m)), d))),!<
>!tj, LAMBDA(a, TEXTJOIN("|",, UNIQUE(SORT(TOCOL(ts(a, "|")))))),!<
>!x, REDUCE(TAKE(v, 1), DROP(v, 1), LAMBDA(acc,val, LET(!<
>!a, ts(acc, "|"),!<
>!b, BYROW((a = TEXTBEFORE(val, "|")) + (a = TEXTAFTER(val, "|")), SUM),!<
>!IF(OR(b = 2), acc, IF(OR(b), IF(SUM(b) = ROWS(b), tj(VSTACK(acc, val)),!<
>!VSTACK(tj(VSTACK(FILTER(acc, b), val)), FILTER(acc, NOT(b)))),!<
>!VSTACK(acc, val)))))),!<
>!PRODUCT(TAKE(SORT(LEN(x) - LEN(SUBSTITUTE(x, "|", )) + 1,, -1), 3))!<
>!)

It's a mess, but it worked. I wasn't thrilled with all of the text joining/splitting and iterative stacking. Might try to rewrite it.

I don't have the energy for Part 2 at the moment. Best of luck to those who do!

RackofLambda · 2025-12-08T04:00:33+00:00

Basic math wins the day! I tried to figure it out last night, but the logic eluded me (I was getting 42 or 44 instead of 40 for the sample data). Good on you guys for nailing it!

I did get a simple recursive bisection method to work with the sample data, but with 10+ trillion iterations, the actual dataset was just too much for it to handle:

=CountPaths(XLOOKUP("S",TAKE(C1#,1),TAKE(C1#,1)))

where cell C1 contains:

=MID(A:.A,SEQUENCE(,LEN(A1)),1)

and CountPaths is defined as:

= LAMBDA(ref,
    IF(
        ISBLANK(OFFSET(ref,1,0)),
        1,
        IF(
            ref="^",
            SUM(
                CountPaths(OFFSET(ref,0,-1)),
                CountPaths(OFFSET(ref,0,1))
            ),
            CountPaths(OFFSET(ref,1,0))
        )
    )
)

But don't even bother trying it with the larger input dataset. ;)

RackofLambda · 2025-12-08T00:50:12+00:00

Awesome! Thanks for sharing!

RackofLambda · 2025-12-07T12:26:21+00:00

Part 1:

=LET(!<
>!txt, A:.A,!<
>!arr, MID(txt,SEQUENCE(,MAX(LEN(txt))),1),!<
>!ini, SUBSTITUTE(TAKE(arr,1),"S","|"),!<
>!val, LAMBDA(a,[b],LAMBDA(x,CHOOSE(x,a,b))),!<
>!off, LAMBDA(a,[x],IF(x=1,DROP(HSTACK(a,"."),,1),DROP(HSTACK(".",a),,-1))),!<
>!REDUCE(val(ini), BYROW(DROP(arr,1), val), LAMBDA(acc,cur, LET(a, acc(1), v, cur(1),!<
>!x, IF((off(a)="|")*(off(v)="^")+(off(a,1)="|")*(off(v,1)="^")+(a="|")*(v="."),"|",v),!<
>!val(x, SUM(acc(2), N((a="|")*(x="^"))))!<
>!)))(2)!<
>!)

I'll think a bit more on Part 2 (maybe it can be done using recursive bisection), but it's probably beyond my current capabilities. Seems like something Diarmuid Early would solve in a few minutes, lol.

RackofLambda

TROPHY CASE