VBA custom sort by category total

[mbk1623]

Tried my best to figure out the best way to do this and came up short.

My data looks something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Category 1[/TD]
[TD]Category 2[/TD]
[TD]Account Desc[/TD]
[TD]FY13[/TD]
[TD]FY14[/TD]
[TD]FY15[/TD]
[/TR]
[TR]
[TD]Revenue
[/TD]
[TD]Product 1
[/TD]
[TD]Sales[/TD]
[TD]100[/TD]
[TD]110[/TD]
[TD]115[/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD]Product 3[/TD]
[TD]Sales[/TD]
[TD]100[/TD]
[TD]150[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Cost of Revenue[/TD]
[TD]Product 1[/TD]
[TD]COGS[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Cost of Revenue[/TD]
[TD]Product 2[/TD]
[TD]COGS[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Cost of Revenue[/TD]
[TD]Product 2[/TD]
[TD]Returns[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD]Product 1[/TD]
[TD]Returns[/TD]
[TD]-20[/TD]
[TD]-30[/TD]
[TD]-50[/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD]Product 2 [/TD]
[TD]Sales[/TD]
[TD]90[/TD]
[TD]90[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Cost of Revenue[/TD]
[TD]Product 3[/TD]
[TD]COGS[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Cost of Revenue[/TD]
[TD]Product 3[/TD]
[TD]Reserves[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


I'll be sorting the "Category1" column based on a customer sort order, which I will perform using CustomOrder: within the sort function, but the part I' stuck on is I'd also like to be able to sort within each unique field in "Category 1" in descending order based on the totals found in the columns "FY13", "FY14", and FY15", (where the fields are sorted by each Fiscal Year in that order since there will often be cases where some data might have null values in some Fiscal Years.)

For example, "Revenue, Product 1, Sales" is the second highest value in FY15 for Revenue, but when combined with "Revenue, Product 1, Returns", Product 1 is now the lowest amount among the 3 products. As such, i want all Product 1 values to appear last within the Revenue group. I want Cost of Sales to have the same logic independently applied.

I will also be sorting "Account Description" from highest to low within each grouping, but since this is my most granular field, I can just sort in descending order for each Fiscal Year before doing any of the above.

This is a simplified example, but I will be looping through many more fields than listed here, which will always change depending on the data.

Appreciate any help!