Forum Discussion
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 name arranged from highest to lowest value?
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))
10 Replies
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).
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!
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.
melyoni Here is one way.
- Not yet. Perhaps if Microsoft adds matrix functions to join two arrays into a single matrix.
