Forum Discussion

DaeyunPablo's avatar
DaeyunPablo
Brass Contributor
Oct 20, 2023
Solved

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

 

EDIT: I didn't specify the following conditions earlier.

  • 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
  • The formula should be not broken when rows are added or deleted; users will add or delete rows and edit the criteria

 

Hi Excel experts! 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)
  • 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. (https://superuser.com/questions/1713274/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.

excel
Formulas and Functions
office 365
  • mtarler's avatar
    mtarler
    Oct 23, 2023

    you should be able to have nested Lambda functions. not sure what issue you had.
    You could also just use the Project_Duration, Criteria1, Criteria2 as inputs:

    =LAMBDA( ProjectDuration, Criteria1, Criteria2, 
   LET(criteria, --FILTER(TAKE(Criteria1:ProjectDuration, , -COLUMNS(ProjectDuration)),
                     (TAKE(Criteria1:ProjectDuration, , 1) = Criteria1) * (TAKE(Criteria2:ProjectDuration, , 1) = TEXTBEFORE(Criteria2, " Subtotal"))), 
       MMULT(SEQUENCE(, ROWS(criteria)) ^ 0, criteria)))


    and BTW this worked for me:

    =LAMBDA(ProjectDuration, Criteria1, Criteria2,
    LET(criteria, --FILTER(TAKE(Criteria1:ProjectDuration, , -COLUMNS(ProjectDuration)),
                         (TAKE(Criteria1:ProjectDuration, , 1) = Criteria1) * (TAKE(Criteria2:ProjectDuration, , 1) = TEXTBEFORE(Criteria2, " Subtotal"))), 
    BYCOL(criteria, LAMBDA(c, SUM(c)))))

     

16 Replies

  • djclements's avatar
    djclements
    Silver Contributor
    Oct 21, 2023

    Assuming the criteria will never evaluate to 1 (TRUE) in the header row (A7:B7), you could use the following formula:

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

     

    If you're concerned about an erroneous match ever occurring in the header row, use the DROP function to remove it when declaring the arr variable:

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

     

    Also, if there's a chance that users may insert columns anywhere between column A and D (or delete column C), then you should make the [columns] parameter of the second DROP function dynamic:

    =LET(
arr, DROP($D$7#:A10, 1),
data, FILTER(DROP(arr,, COLUMNS(arr)-COLUMNS($D$7#)),
   (CHOOSECOLS(arr, 1)=A10)*(CHOOSECOLS(arr, 2)=TEXTBEFORE(B10, " Subtotal"))),
BYCOL(data, LAMBDA(c, SUM(c))))

     

    This was adapted from my original response to your same question posted here: https://superuser.com/questions/1813436/dynamic-array-subtotal-that-changes-its-column-sizes... 

    • mtarler's avatar
      mtarler
      Silver Contributor
      Oct 21, 2023
      don't you get a circular reference error? That said a simple DROP(.... , -1,) should solve that. Not sure why I didn't use it in the above:
      =BYCOL(FILTER(DROP($D$6:$D10,-1,):TAKE($D$6#,,-1),(DROP($A$6:$A10,-1,)=$A10)*(DROP($B$6:$B10,-1,)=TEXTBEFORE($B10," Subtotal")),0),LAMBDA(c, SUM(c)))
      • djclements's avatar
        djclements
        Silver Contributor
        Oct 22, 2023

        mtarler No need to worry about a circular reference occurring in this case, because the filter criteria TEXTBEFORE(B10, " Subtotal") will never evaluate to TRUE on row 10 (or on any subtotal row where the formula is copied to). Even if you were to mistakenly omit the " Subtotal" label in cell B10, TEXTBEFORE would simply return the #N/A error, which cannot be processed by the FILTER function, so no circular reference will occur.

         

        The same principle applies to the SUMIFS function. For example, if the following formula was used in cell D10, it would process correctly without producing a circular reference warning, because the criteria does not evaluate to TRUE on row 10:

         

        =SUMIFS($D$8:$D$26, $A$8:$A$26, A10, $B$8:$B$26, TEXTBEFORE(B10," Subtotal"))

         

        On a side note, seeing the TAKE function in your solution made me realize I can use TAKE, rather than DROP, to simplify the third example in my original post as follows:

         

        =LET(
arr, DROP($D$7#:A10, 1),
data, FILTER(TAKE(arr,, -COLUMNS($D$7#)),
   (CHOOSECOLS(arr, 1)=A10)*(CHOOSECOLS(arr, 2)=TEXTBEFORE(B10, " Subtotal"))),
BYCOL(data, LAMBDA(c, SUM(c))))

         

        TAKE(arr,, -COLUMNS($D$7#)) is much better than DROP(arr,, COLUMNS(arr)-COLUMNS($D$7#)), so thank you for that! 😉

  • mtarler's avatar
    mtarler
    Silver Contributor
    Oct 20, 2023

    so I think this is very possible with a caveat or 2. So just to understand you want a formula in column D that will create a subtotal of the items ABOVE it that have the same criteria in columns A and B and will extend across as many columns as the helper array (D5#). I emphasize the 'ABOVE' because otherwise it will give a circular reference error. but something like:
    =BYCOL(FILTER($D$8:index(9:9,1,3+COLUMNS($D$6#)),($A$8:$A9=$A10)*($B$8:$B9=TEXTBEFORE($B10," Subtotal")),0),LAMBDA(c, sum(c)))

    edit: this should also work:

    =BYCOL(FILTER($D9:TAKE($D$6#,,-1),($A$6:$A9=$A10)*($B$6:$B9=TEXTBEFORE($B10," Subtotal")),0),LAMBDA(c, sum(c)))

     

    • DaeyunPablo's avatar
      DaeyunPablo
      Brass Contributor
      Oct 20, 2023

      Hi mtarler, thank you for your solution. It does work dynamically which is great.
      One other condition I didn't make obvious (which I think is related to 'ABOVE' and circular reference error you mentioned) was that there will be thousands of criteria in column A and B, so I need a general formula that I can copy paste freely within the column D.

      In addition, users will add/delete rows and edit criteria, so I need a formula that wouldn't break if rows are added or deleted. For that reason, I had the column A and B in SUMIFS or whole criteria 1 and 2 ($A$8:$A$26 and $B$8:$B$26) or MMULT, TRANSPOSE, and FILTER combo as the criteria range.
      Your formula needs a manual range adjustments each time if I copy paste to a row below, and it may give #REF error if a certain row is deleted (for example, a row right above a subtotal formula). I see you emphasized the 'ABOVE' because of these issues. Is it possible to handle them as well?

      • mtarler's avatar
        mtarler
        Silver Contributor
        Oct 20, 2023
        so my formulas above should work if you copy and paste anywhere in column D and if you insert or delete rows with the only sensitive/critical row being the row immediately above a subtotal (as you noted). That said try this variant which is getting rather convoluted....:
        =BYCOL(FILTER(DROP(TAKE(TAKE($C$1:$C10,-2,),1):TAKE($D$6#,,-1),,1),(TAKE($A$6:$A10,ROWS($A$6:$A10)-1,)=$A10)*(TAKE($B$6:$B10,ROWS($B$6:$B10)-1)=TEXTBEFORE($B10," Subtotal")),0),LAMBDA(c, sum(c)))

Resources