Sorting unique rows by a related SUMIF column

Question

I am putting together a worksheet to keep track of expenses. I currently have the following source table called "Expenses":

     A       B              C           D
1  Date    Description    Category    Amount
2  1-Jan   Purchase1      Cat1        1.00
3  3-Jan   Purchase2      Cat1        2.00
4  5-Jan   Purchase3      Cat2        3.00
5  7-Jan   Purchase4      Cat2        4.00

And a Summary table:

     A           B
1  Category    Total
2  Cat1        3.00
3  Cat2        7.00

The formulas in the Summary table are:

A2: =UNIQUE(Expenses!$C$2:$C$5)
A3: =CONTINUE(A2, 2, 1)
B2: =SUMIF(Expenses!$C$2:$C$5,A2,Expenses!$D$2:$D$5)
B3: =SUMIF(Expenses!$C$2:$C$5,A3,Expenses!$D$2:$D$5)

What I would like to achieve is sorting the Summary table by the value in column B in decreasing order, but I can't figure out how to get the SUMIF() value in array form to use with SORT().

Eventually I hope to have a formula for A2 that looks something like:

=SORT(UNIQUE(Expenses!$C$2:$C$5, ***ARRAY_OF_SUMIF_VALUES***, FALSE)

Answer 1

I have had a similar issue, with trying to sort a UNIQUE dataset.

the way i got around it was by creating another sheet and using the '=QUERY()' function to call the sheet with the UNIQUE list on it. This way you can add a ORDER BY statement to the query.

so in your example, Expenses has the source data, the summary table has the Summary data (with the UNIQUE function),

and then add a new sheet called "Summary Sorted" and then add the query function like this:

=query(Summary!A1:B4, "SELECT A,B ORDER BY B DESC")

Here is an example spreadsheet to demonstrate: