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)))))
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...
- 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)))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! 😉
djclements so that is interesting. When I first tried it I thought I got an error like #VALUE or #NA error which I was assuming was a circular reference error just manifesting that way. I know it wouldn't actually be a circular reference but in the past it seemed excel flagged anything that could. Well, I just cleaned my solution up to not use the DROPs and seems fine. we don't even need that TAKE($D$6#,,-1) so:
=BYCOL(FILTER($D$6#:$D10, ($A$6:$A10=$A10)*($B$6:$B10=TEXTBEFORE($B10," Subtotal")),0), LAMBDA(c, SUM(c)))