Combining rows in dynamic array

[depcdivr]

I am trying to create a dynamic array of data from a large set of data so that I can create an interactive chart. I have the following columns

Unit

Name

Region

Rep

Month

Year

Price

I can create the dynamic array but I run into multiple rows where the first 6 columns are the same but only the last one is different. Is there a way to automatically combine those rows into one where the price is the sum of combinations where the first 6 columns are identical?

For example I have have these three rows in the array

Unit 1	Depcdivr	East	None	Jan	2022	$10,000
Unit 1	Depcdivr	East	None	Jan	2022	$20,000

Unit 1

Depcdivr

East

None

Jan

2022

$15,000

and I want to combine them to be

Unit 1

Depcdivr

East

None

Jan

2022

$45,000

I know this can all be handled by a pivot table the ultimate goal is to create an interactive chart where you can select different sets of data with just a click of a button.

 

[depcdivr]

One last question pertaining to this application. I want to add a sortby so that the data will always be sorted by largest to smallest. I know how the sort by works but what two arguments do I use in it. Well the array would be the formula from above but how would you specify the sortby value?

 

[Fluff]

To sort use

=LET(u,UNIQUE(FILTER(CHOOSECOLS(A2:L6,1,3,4,5,6,7),D2:D6>0)),SORT(HSTACK(u,SUMIFS(L2:L6,A2:A6,CHOOSECOLS(u,1),C2:C6,CHOOSECOLS(u,2),F2:F6,CHOOSECOLS(u,5),G2:G6,CHOOSECOLS(u,6))),7,-1))