4

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)
5

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.