Calculate sum of column x values based on search criteria in column y-CodePudding

Apologies if the title is not accurate I'm not sure how to perfectly explain it without visual help.

I have an excel sheet as follows;

Transaction Name    Amount
Mr Robert           £10
Mr Paul             £20
Mr Greg             £15
Mr Greg             £30
Mr Nordan           £50
Mr Robert           £1
Mr Robert           £1
Mr Robert           £1

I want a formula that will return the top 10 largest total amounts based on unique transaction names like so;

Mr Nordan £50
Mr Greg £45
Mr Paul £20
Mr Robert £13

So even though Mr Robert appears 4 times, his total is the lowest at £13, and Mr Nordan is the highest with £50 even though he only appears on the list once.

I have several hundred unique transaction names so I can't filter them individually and add their sum up manually.

I have managed so far to return a list of the top 10 most frequently occurring transaction names, but I know this isn't accurate with what is the most expensive cost.

Thanks in advance for any help!

CodePudding user response：

Perhaps you could try using the following formula as shown in the screen-print, suggested by

• Formula used in cell D1

=VSTACK(A1:B1, SORT(HSTACK(UNIQUE(A2:A9), SUMIFS(B2:B9,A2:A9,UNIQUE(A2:A9))),2,-1))

For Beginners, this might help to understand the use of LET() & naming variables.

• Formula used in cell D1

=LET(_uniqueTransaction,UNIQUE(A2:A9), _sumofAmt,SUMIFS(B2:B9,A2:A9,_uniqueTransaction), VSTACK(A1:B1,SORT(HSTACK(_uniqueTransaction,_sumofAmt),2,-1)))

Using LET() Function, the key benefits are using naming variables makes it easier to read while computing complex formulas, reduces the redundancy and errors that may arise from having the same code in one place as well as increases the performance of formulas.

Using UNIQUE() Function which returns the list of unique values in a list or a range, here in the example we used the same to return the unique Transaction Names.

_uniqueTransaction UNIQUE(A2:A9)

Using SUMIFS() to return the sum of amounts of all the unique transactions.

_sumofAmt SUMIFS(B2:B9,A2:A9,_uniqueTransaction)

Using HSTACK() to return the combined arrays horizontally into a single array. Each of the subsequent array is appended to the right of the previous array.

HSTACK(_uniqueTransaction,_sumofAmt)

Using the SORT() function which sorts the contents of HSTACK() in descending order. Note the return output with SORT() is a dynamic array of values that will spill, if the values in the source changes, it will update automatically.

SORT(HSTACK(_uniqueTransaction,_sumofAmt),2,-1)

Lastly, we are using VSTACK() Function to return the headers along with the desired output, which combines arrays vertically into an array. Each subsequent array is appended to the bottom of the previous array.

VSTACK(A1:B1,SORT(HSTACK(_uniqueTransaction,_sumofAmt),2,-1))

CodePudding user response：

Lets say Names are in A2:A9 range and Amounts in B2:B9

First of all you need to find unique transactions names. There are several methods, but I will use INDEX/MATCH method this time. It might not work in older EXCEL versions (read how to use array formulas).

Put =IFERROR(INDEX($A$2:$A$9, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$9), 0)),"") in D2 cell and drag it down. Keep in mind that COUNTIF range must start one cell above $D$1:D1.

Next to it (in E2 cell put and drag down formula =SUMIF($A$2:$A$9,D2,$B$2:$B$9) to find total amounts by name.

Put formula =SORT(D2:E5,2,-1) in G2 cell and you will get spilled array sorted by total amount. Output: