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 know 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 (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!

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!

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

=LET(
arr, $D$7#:$A$26,
_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 know 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 $A$26. Basically, the resulting array is referencing FROM the minimum column (A) and row (7) of the two ranges TO the maximum column (dynamic) and row (26) of the two ranges ($A$7:$L$26 in this example). Note: the reason I've used absolute range references 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!

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 know 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 (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!