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

     

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)))
  • DaeyunPablo's avatar
    DaeyunPablo
    Brass Contributor
    Oct 20, 2023
    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?
    • mtarler's avatar
      mtarler
      Silver Contributor
      Oct 20, 2023
      You are very welcome. I learned it all here and on my computer. I read what other did to solve problems and spent time solving others' problems (and my own problems). There are tons of resources online but I really haven't had time to watch, rate, and catalog them.
      • DaeyunPablo's avatar
        DaeyunPablo
        Brass Contributor
        Oct 20, 2023
        Guess I should join you and stay in this community for a while. Please come by and leave a message if you come up with any other brilliant solutions!

Resources