Return to 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)
• 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 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.

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.

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)
• 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 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 decided to stop using VBA as users of the template can be creative to break the macros and they need constant maintenance. It's also time-consuming to build and maintain the code.

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

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)
• 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 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.

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)
• 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 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)))

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

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)
• 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 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 decided to stop using VBA as users of the template can be creative to break the macros and they need constant maintenance. It's also time-consuming to build and maintain the code.

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