Dynamic Array Subtotal that changes its column sizes without using VBA or volatile functions

Question

I need to make a dynamic array subtotal that changes its column size by another dynamic array. The example sheet, challenge, conditions, and attempts I've made are below.

[Example Sheet]

Challenge:

• Make (partial) dynamic subtotal arrays in column D fully dynamic that change their column sizes by another dynamic array
• Modify the range part $C$10:$L$28 in the dynamic subtotal arrays using D5# (helping array)
• It should be a generic formula that copy-paste is available within column D; there will be thousands of criteria 1 and 2 in column A and B
• Avoid using VBA, volatile functions (OFFSET, INDIRECT, INFO, CELL, etc.) or other expensive methods

Pre-requisites:

• Project start/end dates determine the size of the dynamic array D6# (Month-Year)
• D6# is used for the dynamic array D7# (Workhours/month)
• D5# is an helping array to determine the size of the dynamic array subtotals
• Criteria 1 and 2 in column A and B are used for the dynamic array subtotals -The formula should be not broken when rows are added or deleted; users will add or delete rows and edit the criteria
• The dynamic array subtotals sum up the monthly workhours per each criteria 1 and 2

Attempts:

• SUBTOTAL can be only dynamic to show AVERAGE, COUNT, MAX, MIN, SUM, etc. I couldn't think of any way to utilize this function.
• Formula: =SUBTOTAL(SEQUENCE(11),reference range)
• SUMIFS can be dynamic if the criteria ranges are dynamic, which is not the way I wanted; it needs to use a dynamic array to determine the column size. What I have is non-dynamic formula that requires manual copy to the rest columns. (https://stackoverflow.com/questions/69655057/can-one-do-a-sumifs-on-a-dynamic-spilled-range-and-return-a-2d-array)
• Formula: =SUMIFS(D:D,$A:$A,$A10,$B:$B,TEXTBEFORE($B10," Subtotal"))
• MMULT, TRANSPOSE and FILTER combo is the closest solution I came up with, which is partially dynamic that doesn't change its column size automatically. As mentioned above, the range $D$8:$L$26 is the tricky part to modify that somehow it refers to the helping array D5# without using OFFSET, INDIRECT or other volatile functions. At this stage, I need to manually set the formula the range to be summed up. (Excel365 Sumifs with Spilled Dynamic Arrays)
• Formula: =LET(Criteria,($A$8:$A$26=A10)*($B$8:$B$26=TEXTBEFORE($B10," Subtotal")), MMULT(TRANSPOSE(FILTER(Criteria,Criteria=1)),--FILTER($D$8:$L$26,Criteria=1)))
• I'm trying to make the template VBA-free as users can be creative to break the macros in old templates I made. It was also time-consuming to build and maintain the code.

Much appreciated if you can suggest a solution to the struggle I'm facing.

Answer 1

There's a lot to unpack here, but if I've understood you correctly, the following formula should work:

=LET(
arr, $D$7#:A10,
_c1, CHOOSECOLS(arr, 1),
_c2, CHOOSECOLS(arr, 2),
incl, (_c1=A10)*(_c2=TEXTBEFORE(B10, " Subtotal")),
BYCOL(FILTER(DROP(arr,, 3), incl), LAMBDA(c, SUM(c))))

This method utilizes a little known trick with dynamic arrays, whereby the range operator (colon) is used to extend the dimensions of $D$7# to include everything between it and cell A10. Basically, the resulting array is referencing FROM the minimum column (A) and minimum row (7) of the two ranges TO the maximum column (dynamic) and maximum row (10) of the two ranges (which is A7:L10 in this example). Note: the reason I've used an absolute range reference with the dynamic array is so the formula can be copied and pasted in column D for each subtotal row.

CHOOSECOLS() is used to identify each criteria column, and DROP() is used to identify the data range by removing the first 3 columns. BYCOL() is then used to dynamically SUM() each column of the filtered array (so your "helping array" in cell D5 is no longer needed).

Results:

I hope that's what you meant. Cheers!