When #VALUE! isn’t an error: The hidden reference behavior of Excel’s dynamic arrays

Great stuff! Very interesting... thank you for laying it out in complete detail.

Under 2.2 Deferred Evaluation, you've hinted towards "aggregation functions that re-evaluate their inputs individually". So far, I've found the xxIF(S) family functions can handle an "array of ranges", as well as SUBTOTAL (but not AGGREGATE). Are you aware of any others?

Examples:

// Running count by category
= LET(
    cat, TOROW(UNIQUE(SORT(Category))),
    VSTACK(cat, COUNTIFS(TAKE(Category, SEQUENCE(ROWS(Category))), cat))
)

// Running total by category
= LET(
    cat, TOROW(UNIQUE(SORT(Category))),
    VSTACK(cat, SUMIF(TAKE(Category, SEQUENCE(ROWS(Category))), cat, Amount))
)

// Running total by row and column
= SUBTOTAL(9, TAKE(range_2d, SEQUENCE(ROWS(range_2d)), SEQUENCE(, COLUMNS(range_2d))))

// Running total by row and column, last to first
= SUBTOTAL(9, TAKE(range_2d, SEQUENCE(ROWS(range_2d),, ROWS(range_2d), -1), SEQUENCE(, COLUMNS(range_2d), COLUMNS(range_2d), -1)))

// Running total by row --> equivalent to: = SCAN(, BYROW(range_2d, SUM), SUM)
= SUBTOTAL(9, TAKE(range_2d, SEQUENCE(ROWS(range_2d))))

// Running total by column --> equivalent to: = SCAN(, BYCOL(range_2d, SUM), SUM)
= SUBTOTAL(9, TAKE(range_2d,, SEQUENCE(, COLUMNS(range_2d))))

// Running total by n x n block
= SUBTOTAL(9, TAKE(range_2d, {1;2;3;4}, {1;2;3;4}))  // vertical output
= SUBTOTAL(9, TAKE(range_2d, {1,2,3,4}, {1,2,3,4}))  // horizontal output

// Running total for a targeted section
= SUBTOTAL(9, TAKE(DROP(range_2d, 1, 1), {1;2;3}, {1,2}))