SOLVED

dynamic array excel sum

Copper Contributor

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 name arranged from highest to lowest value?

 

10 Replies

@melyoni Here is one way.

@Jan Karel Pieterse 

Thanks!

no way to do it with out the middle table?

Not yet. Perhaps if Microsoft adds matrix functions to join two arrays into a single matrix.
best response confirmed by melyoni (Copper Contributor)
Solution

@Jan Karel Pieterse 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))

@mtarler 

amazing! this is exactly what I was looking for!

thanks

Nice solution. Though I'm not a fan of such convoluted formulas.

@Jan Karel Pieterse 

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.

 

 

@melyoni 

As a comment, in Name Manager I see lot of references on external resources. Perhaps Power Query will be more optimal solution for entire project (not for this concrete task).

@Sergei Baklan 

I don't know.  There is a huge amount of overlapping functionality between the worksheet formula (including named formulas) and Power Query (one also could add VBA and TypeScript to the mix). I tend to restrict Power Query to situations in which there is a clear need to import data with a well-defined ETL requirement. 

 

On one occasion you pointed out that PQ is capable of far more than that and can be used for calculation, though I still tend to turn to the spreadsheet formula for that.  PQ gives the appearance of being a no-code environment but that only gets you so far.  Then one turns to a somewhat 'wordy' and cumbersome programming language, but well-supported by a multitude of high-level operations that I cannot start to remember right now.  

 

Something that PQ does have is impressive functionality when it comes to developing and testing queries, in that one can step through complex command sequences / formulas inspecting the results at each step.  Now, as worksheet formulas are getting more complex, I would like to see the equivalent applied to the worksheet; Evaluate Formula on steroids!

@Peter Bartholomew 

In general I didn't mean Power Query only of formulas only. Having lot of external files it's logical to use power query as structured data source for tables and/or data model, even without any or with minimum of transformation. That's normal practice to use combination of PQ, data model and formulas in one project, in which proportions it depends on nature of data and required reports.

1 best response

Accepted Solutions
best response confirmed by melyoni (Copper Contributor)
Solution

@Jan Karel Pieterse 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))

View solution in original post