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