Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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)

I appreciate any and all suggestions. Thanks.

share|improve this question

1 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:

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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