Change sort on the Transaction Description Sum By Category on the Cat Tracker

[bkmccarty]

I’d like to change the sort by Amount instead of alphabetical in the Cat Tracker Report.
I tried changing the ORDER BY in the following formula to &I3& but that failed.

=iferror(QUERY(Transactions!A:AE,“SELECT " &I1& " , SUM(” &I3 & “) WHERE " &I4& " >= DATE “”” & text(E4, “yyyy-mm-dd”) & “”" AND " &I4& " <= DATE “”" & text(E5, “yyyy-mm-dd”) & “”" AND " &I2& " = ‘" & E3 & "’ GROUP BY " &I1& " ORDER BY " &I1& " ASC LABEL SUM(" &I3 & “) ‘’, " &I1& " ‘’”, -1),“”)

Any tips for getting me started, I’m trying to figure out the items in the hidden columns but the light bulb has not turned on yet

[warren]

@bkmccarty Check out this conversation… it may help.

Category Tracker Report sorting

On the Category Tracker tab, the report is sorted alphabetically by the category. Instead, I would like to sort it by the column B which is the $ amount. Unfortunately when I sort column B (A-Z or Z-A) all the categories disappear. They all come back only if column A is sorted again. Can column B be sorted?

[bkmccarty]

Thanks but I saw that conversation. It speaks to the formula in column A. I’m talking about the formula in Column D7

[swtconsulting]

Would love to see solution here, I think my category tracker sheet is different than the topics covered here. I have attempted to sort by amount vs category by changing ORDER BY Col1 to ORDER BY Col 3, but this does not work. BTW, Col1 refers to Category and Col3 refers to amount. Can the developer create new logic where the option to sort by category or amount is in the feature set?

[swtconsulting]

Figured it out, The column is really called sum(Col3), so when you change the ORDER BY clause from ORDER BY Col1 to Col3 it needs to read ORDER BY Sum(Col3)

[adkisoncc]

I am interested in changing the order at which the data is displayed from alpha numeric to by highest to lowest amount. I see above this has already been asked and answered but it seems like the sheet has changed from the last time this was asked. If I’m in the correct location, the formula is in cell A12 and is:
=IFERROR(SORT(FILTER($L$12:$L,N12:N<>"Hide",L12:L<>"")))

what do I change here to get the data to sort from highest to lowest amount?

[randy]

The SORT() function is documented here.

So it may be as simple as adding a FALSE to the is_ascending field. You could experiment with something like this:
=IFERROR(SORT(FILTER($L$12:$L,N12:N<>“Hide”,L12:L<>“”)), FALSE)

[adkisoncc]

unfortunately when I added the FALSE into A12 nothing happened. I wanted to make sure you understood my question thought…I’m trying to change the order from alpha numeric in the “category” column to highest to lowest expense under the “Amount” category.

What cell should I be making the change in and what is the change?

[randy]

Maybe this then?
=IFERROR(index(SORT(FILTER({$L$12:$L,$M$12:$M},N12:N<>"Hide",L12:L<>""),2,false),,1))

[adkisoncc]

thank you very much…what you provided worked, I just needed to change false to true and it ordered it so I could see my biggest expense drivers on top vice my biggest income drivers.

[phil.stallone]

Wow, very nice. This worked. Now I can see the expenses from high amount to low. Much appreciated!
Now, any idea how I can do the same on the Monthly Budget tab?

[jdinning]

Thanks for the idea here. I created a data validation dropdown box in B10 with the following sort order choices: “Category”, “Income First” and “Expense First”. Then I put the following formula in cell A12:

=if(B10=“Category”,IFERROR(SORT(FILTER($L$12:$L,N12:N<>“Hide”,L12:L<>“”))),if(B10=“Income First”,IFERROR(index(SORT(FILTER({$L$12:$L,$M$12:$M},N12:N<>“Hide”,L12:L<>“”),2,false),1)),IFERROR(index(SORT(FILTER({$L$12:$L,$M$12:$M},N12:N<>“Hide”,L12:L<>“”),2,true),1))))

This allows me to select which of the three sort orders I want from the dropdown list.

[stephensandlin]

This is a great solution. I updated my sheet to allow sorting options for the Categories on the left, and the Category highlight on the right.

New cell formulas:

A12:

=IF(F4="Category",IFERROR(SORT(FILTER($L$12:$L,N12:N<>"Hide",L12:L<>""))),IF(F4="Income First",IFERROR(index(SORT(FILTER({$L$12:$L,$M$12:$M},N12:N<>"Hide",L12:L<>""),2,false),,1)),IFERROR(index(SORT(FILTER({$L$12:$L,$M$12:$M},N12:N<>"Hide",L12:L<>""),2,true),,1))))

I12:

=IF(F5="Amount",IFERROR(QUERY({INDIRECT(O2),INDIRECT(O3),INDIRECT(O4),INDIRECT(O5)},"SELECT Col1, SUM(Col3) WHERE Col4 >= DATE """ & text($E$2, "yyyy-mm-dd") & """ AND Col4 <= DATE """ & text($G$2, "yyyy-mm-dd") & """ AND Col2 = """ & I8 & """ GROUP BY Col1 ORDER BY Sum(Col3) ASC LABEL SUM(Col3) '', Col1 ''", -1)),IFERROR(QUERY({INDIRECT(O2),INDIRECT(O3),INDIRECT(O4),INDIRECT(O5)},"SELECT Col1, SUM(Col3) WHERE Col4 >= DATE """ & text($E$2, "yyyy-mm-dd") & """ AND Col4 <= DATE """ & text($G$2, "yyyy-mm-dd") & """ AND Col2 = """ & I8 & """ GROUP BY Col1 ORDER BY Col1 ASC LABEL SUM(Col3) '', Col1 ''", -1)))

E4:

⬅️ Sort Categories by:

E5:

➡️ Sort Category Highlight by:

F4:

Dropdown with 3 choices: Category, Income First, Expense First

F5:

Dropdown with 2 choices: Amount, Name

[braxton.ponder]

Here’s a one-cell fix that also:

1. Filters the list of Categories to show only what is selected in F8 (Expense or Income)
2. Sorts so the largest magnitude is at the top (because Expenses are negative numbers, they need to use “true” whereas Income needs to use “false” to achieve this)

Replace A12 with:

=IFERROR(INDEX(SORT(FILTER({L$12:L,M$12:M},N12:N<>“Hide”,L12:L<>“”,O12:O=F$8),2,IF(F$8=“Income”,false,true)),1))

By the way, I love this sheet, especially with this tweak, but I also wanted a count of any uncategorized transactions, rather than just the total uncategorized, which it already shows. That way, at a glance I know how many transactions my AutoCat didn’t catch.

B9: add the formula:
=COUNTIFS(Transactions!D2:D,“=”,Transactions!B2:B,“<>”,Transactions!B2:B,“>=”&IF(E2=“”,DATE(1900,1,1),E2),Transactions!B2:B,“<=”&IF(G2=“”,DATE(9999,12,31),G2))

You can also do conditional formatting on that cell so that the text is red if it’s not zero.

Even easier: copy and paste C17 cell from the Tiller “Spending Trends” sheet, and that will paste in the formula and the conditional formatting at the same time.

Exclude Categories:
I went further with this and set up the ability to **exclude categories from the pie chart. (**If you wanted to exclude them from the category list at A12, it can be done but requires more a few more edits for minimal gain).

For me this was useful because I had an unusually large tax bill due to a property sale, and that category was overwhelming the chart.

Create a new dropdown from a range at F10 and refer to the range =A12:A to populate the dropdown.

Update the formula at E12 (from which the chart gets it’s data)
=IFERROR(QUERY({IF(E8=“Category”,L12:L,P12:P),M12:M,O12:O,N12:N},“SELECT Col1,”&IF(F8=“Expense”,“-1*”,“”)&“SUM(Col2) WHERE Col3=”“”&F8&“”" AND Col4<>““Hide””“&IF(F10=”“,”“,SUBSTITUTE(” AND Col1<>‘“&SUBSTITUTE(F10,”, “,”’ AND Col1<>‘“&”“)&”’“,”’ AND Col1<>‘’“,”“))&” GROUP BY Col1 ORDER BY “&IF(F8=“Expense”,”-1*“,”“)&“SUM(Col2) DESC LIMIT “&G8&” LABEL “&IF(F8=“Expense”,”-1*”,”")&“SUM(Col2) ‘’”))

This formula will handle a multi-select dropdown at F10. I only needed to exclude one category and preferred the aesthetics of the single choice list, but if you want multi-choice, this will still work.