Forum Discussion

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

excel

Formulas and Functions

office 365

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

DaeyunPablo

Brass Contributor

Oct 23, 2023

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:

mtarler

Silver Contributor

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

DaeyunPablo
Brass Contributor
Oct 23, 2023
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 🙂