Oct 20 2023 09:53 AM - edited Oct 20 2023 12:24 PM
EDIT: I didn't specify the following conditions earlier.
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:
Pre-requisites:
Attempts:
Much appreciated if you can suggest a solution to the struggle I'm facing.
Oct 20 2023 10:14 AM - edited Oct 20 2023 10:22 AM
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)))
Oct 20 2023 12:04 PM - edited Oct 20 2023 12:28 PM
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?
Oct 20 2023 01:15 PM
Oct 20 2023 02:04 PM
Oct 20 2023 02:26 PM
Oct 20 2023 03:09 PM
Oct 21 2023 03:49 AM
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...
Oct 21 2023 01:42 PM
Oct 21 2023 11:29 PM - edited Oct 21 2023 11:34 PM
@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! ;)
Oct 22 2023 09:21 AM - edited Oct 22 2023 09:33 AM
@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)))
Oct 22 2023 07:53 PM
@mtarler Great! That now looks like the simplest possible solution! :)
Oct 23 2023 06:54 AM
@mtarler, @djclements Appreciate the work of two great minds, it is indeed the simplest solution I didn't expect to be possible :)
Oct 23 2023 08:39 AM - edited Oct 23 2023 09:34 AM
@mtarler Looking at your elegant solution inspired by @djclements, I found a way to make my last attempt formula fully dynamic below:
=LET(Criteria,($A$5:$A$25=A9)*($B$5:$B$25=TEXTBEFORE($B9," Subtotal")),
MMULT(TRANSPOSE(FILTER(Criteria,Criteria=1)),--FILTER($D$5#:$D$25,Criteria=1))
)
By simply changing the range from $D$8:$L$26 to $D$5#:$D$25 where it's the combo of the spilled range and the last row & first column cell, it works as intended. I also could make a custom function out of this (named MONTHLYTOTAL):
=LAMBDA(criteria1_range,criteria1,criteria2_range,criteria2,project_duration,last_cell,
LET(Criteria,(criteria1_range=criteria1)*(criteria2_range=TEXTBEFORE(criteria2," Subtotal")),
MMULT(TRANSPOSE(FILTER(Criteria,Criteria=1)),--FILTER(project_duration:last_cell,Criteria=1)))
)
The custom function below can be copiable in column D :
=MONTHLYTOTAL($A$5:$A$25,A9,$B$5:$B$25,$B9,$D$5#,$D$25)
I couldn't convert both of your solutions into custom functions. I'm guessing those two have LAMBDA already and you can't have a nested LAMBDA function...?
It would be great if I can avoid using the last_cell ($D$25 in $D$5#:$D$25) somehow by referring to the criteria1 range instead, but I'm just happy with all of the solutions here working well :)
Oct 23 2023 09:52 AM - edited Oct 23 2023 10:13 AM
@mtarler Alternatively, I revised your formula that refers to the subtotal cell itself (D9) which I think is neater than referring to the last cell of the monthly hours table.
=LET(CriteriaArray,--FILTER(D5#:$D9,($A$5:$A9=$A9)*($B$5:$B9=TEXTBEFORE($B9," Subtotal"))),
MMULT(SEQUENCE(,ROWS(CriteriaArray))^0,CriteriaArray)
)
The custom function will be:
=LAMBDA(
project_duration,monthly_subtotal_cell,criteria1_begin,criteria1,criteria2_begin,criteria2,
LET(CriteriaArray,--FILTER(project_duration:monthly_subtotal_cell,(criteria1_begin:criteria1=criteria1)*(criteria2_begin:criteria2=TEXTBEFORE(criteria2," Subtotal"))),
MMULT(SEQUENCE(,ROWS(CriteriaArray))^0,CriteriaArray))
)
It looks like below:
Oct 23 2023 10:45 AM - edited Oct 23 2023 10:48 AM
Solutionyou 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)))))
Oct 23 2023 12:06 PM - edited Oct 23 2023 12:22 PM
@mtarler It can be that compact with three inputs only! Wonder which one is faster but I guess both are almost the same as the structures are quite similar (not very expensive as well). I'll use them well, thanks a million :)
Oct 23 2023 10:45 AM - edited Oct 23 2023 10:48 AM
Solutionyou 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)))))