Forum Discussion
Dynamic Array Subtotal that changes its column sizes without using VBA or volatile functions
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)))))
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)))
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?- 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)))- It works like a charm, thank you so much 🙂 I'll spend some time to understand how it works and see if I can make it less convoluted, if not I'll just make and use it as a custom function.
Side question, where did you learn these array functions knowledge? Could you recommend a UDEMY course or something for this kind of advanced ones?
