Скачать или смотреть Create Subtotals for Dynamic Spilled Arrays in Excel

Alternative to PIVOTBY to get Subtotals from Dynamic Spilled Array Data.
Steps:
1. Create named variables for the Last Row, Data, Sort Columns, and Values
2. Sort data using named variables into spilled array
3. Add number column to spilled array
4. Create unique list of subtotal categories and max numbers
5. Create Final table for categories, sum amounts, and max+ numbers
6. Combine data and subtotals, then sort by numbers
7. Add Conditional formatting

(Last Row) =LOOKUP(2,1/(NOT(ISBLANK(Data!$A:$A))),ROW(Data!$A:$A))



~~~~~~~~~~~~~~~~~~~~~~~~~
Please checkout my book, Power Query M Language Basics Kindle Edition here -˃
https://www.amazon.com/dp/B0DM73H3RV

~~~~~~~~~~~~~~~~~~~~~~~~