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:

Category 1	Category 2	Account Desc	FY13	FY14	FY15
Revenue	Product 1	Sales	100	110	115
Revenue	Product 3	Sales	100	150	300
Cost of Revenue	Product 1	COGS	20	30	30
Cost of Revenue	Product 2	COGS	20	20	20
Cost of Revenue	Product 2	Returns	5	6	4
Revenue	Product 1	Returns	-20	-30	-50
Revenue	Product 2	Sales	90	90	0
Cost of Revenue	Product 3	COGS	5	8	9
Cost of Revenue	Product 3	Reserves	10	10	10

<tbody>
</tbody>

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!