Forum Discussion
Solved
dynamic array excel sum
Hi, I have a list with names and amounts. Each name appears several times, and not all of them have amounts (attached). How (using dynamic array) can I create a list of the total amounts by ...
JKPieterse why not using LET()?
=LET(comp,UNIQUE(Table1[Company Name]),totals,SUMIFS(Table1[Total outstanding anount],Table1[Company Name],comp),tab,CHOOSE({1,2},comp,totals),SORT(tab,2,-1))
Not yet. Perhaps if Microsoft adds matrix functions to join two arrays into a single matrix.
JKPieterse why not using LET()?
=LET(comp,UNIQUE(Table1[Company Name]),totals,SUMIFS(Table1[Total outstanding anount],Table1[Company Name],comp),tab,CHOOSE({1,2},comp,totals),SORT(tab,2,-1))- Nice solution. Though I'm not a fan of such convoluted formulas.
I think it is a question of getting used to the formulas. They are probably easier to assimilate when spread over several lines
= LET( comp, UNIQUE(Table1[Company Name]), totals, SUMIFS(Table1[Outstanding anounts],Table1[Company Name],comp), table, CHOOSE({1,2},comp,totals), SORTBY(table,totals,-1))I actually find such formulas less convoluted than the equivalent traditional formula because the nesting can be largely removed. I have pitched for a distinct separator for the assignment within each pair of parameters, e.g.
= LET( comp := UNIQUE(Table1[Company Name]), totals := SUMIFS(Table1[Outstanding anounts],Table1[Company Name],comp), table := CHOOSE({1,2},comp,totals), SORTBY(table,totals,-1))but I wouldn't hold my breath for that.
