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 Purchase1 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)
Improve this question

3 Answers 3

Reset to default
6

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:

Improve this answer
1
  • There is no need to add the additional sheet. OP can use a single QUERY function to generate the original table with the preferred sort.
    – Blind Spots ♦
    2 days ago
2

I'm running into exactly the same thing, and I also did a workaround but I didn't make another sheet or use the QUERY() function.

I put the contents of my UNIQUE() function into a separate column, and then referenced the copied unique values in the column where I wanted my summary to print out. It looked like this:

     A       B              C           D            E                      F             G                                       H                                     I
1  Date    Description    Category    Amount    Unique Cats           Unique Again      Total                             Sorted Unique Cats                      Sorted Total
2  1-Jan   Purchase1      Cat1        1.00      Cat1 [=UNIQUE(C2:C5)] Cat1 [=E2]        3.00 [=SUMIF(C2:C5, E2, D2:D5)]   Cat2 [=SORT(F3:G4, G3:G4, FALSE)]     7.00
3  3-Jan   Purchase2      Cat1        2.00      Cat2                  Cat2 [=E3]        7.00                              Cat1                                    3.00
4  5-Jan   Purchase3      Cat2        3.00
5  7-Jan   Purchase4      Cat2        4.00

So you can see I'm referencing Column F in my sort, and not Column E. I think the issue in my case, and maybe also in yours, is that the SUMIF function is relying on the output from the UNIQUE function, and SORT is changing what's output from the UNIQUE function in Column A in your example. By copying the UNIQUE data into another column, the SUMIF will continue to point to the original UNIQUE output (Column E in my example table below), and then the SORT function can work on Columns F and G without changing the reference to the output of the UNIQUE function within the SUMIF.

I don't think that's particularly well explained and maybe also just not right, but that's my story at the moment. I'm happy to hear a better one if somebody has one. In the meantime, hopefully this works for you!

Improve this answer
New contributor
bweisberger is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
3
  • FYI the question isn't about sorting the data into new columns. It is about sorting the table itself. The OP was correct they need to use some kind of array formula. My answer is an example using QUERY and I also added one using BYROW. Look at it to better understand the issue the OP has and the solution.
    – Blind Spots ♦
    2 days ago
  • Ah, the SORTED function was a typo, should have been SORT. I'll fix that now, thanks for pointing it out.
    – bweisberger
    yesterday
  • 👍 Updated my comment to reflect your fix. FYI there is no benefit to duplicating E to F. Also, splitting columns G & H is unecessary: =SORT(SUMIF(C2:C5, E2, D2:D5),2,0). That aside, the OP is looking to solve a different problem. They want to sort their table not create an additional table with their data sorted. Otherwise, they could simply use =SORT(table, 2,0) to get a version sorted by column 2 descending anywhere they wanted to put the formula (same sheet, different sheet, etc.).
    – Blind Spots ♦
    yesterday
0

Summary Table

You are generating the summary table using multiple formulas but the entire table including headings and sort options can be generated using a single QUERY formula in A1.

Single QUERY Formula

=QUERY(Expenses!C1:D5, 
   "Select C, SUM(D) Group By C
    Order By SUM(D) Desc
    Label SUM(D) 'Total'")

Infinite Range Formula

C1:D instead of C1:D5. Automatically adds new rows from source sheet to Summary Table.

=QUERY(Expenses!C1:D, 
   "Select C, SUM(D) 
    Where C is not Null 
    Group By C
    Order By SUM(D) Desc
    Label SUM(D) 'Total'")

BYROW Formula

=SORT(
   BYROW(
     UNIQUE(C2:C5), 
     LAMBDA(r,
       {r, SUMIF(C2:C,r,D2:D)})),
   2,0)
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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