Using sum + unique functions in dynamic arrays

[mach82]

Hi
I've been playing with dynamic arrays in Excel 365, but there's one thing that I can't figure out.
I have some data in the A:D columns, out of which I want to receive unique values in F:H columns. I use "Choose" function to omit the "Sales Date" column, as I don't need in this specific place.
I'd however like to get the aggregated sales value in the H column, so basically the end result should be Gary MIller from finance with sales value 60270 and Richard Elliot from Finance with sales value 30000.
How should I redifine the formula to achieve my goal? I later on need to sort by sales value descending and I have to do that within one dynamic array formula.

F2=SORT(UNIQUE(CHOOSE({1\2\3};A2:A4;B2:B4;D2:D4));3;-1)

I apprecaite your help a lot.

 

[CA_Punit]

This is not the optimum solution. But just an option

New Microsoft Excel Worksheet.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N
3
4
5	Department	Name	Sales Data	Sales Value				Step 1
6	Finance	Garry miller	2019	60270					Finance	Garry miller	60270
7	Finance	Richard Elliot	2020	10000					Finance	Richard Elliot	30000
8	Finance	Richard Elliot	2019	20000					HR	Punit	10000
9	HR	Punit	2019	10000					HR	Rahul	20000
10	HR	Rahul	2020	20000
11								Step 2
12									Finance	Garry miller	60270
13									Finance	Richard Elliot	30000
14									HR	Rahul	20000
15									HR	Punit	10000
16
17
Sheet1

Cell Formulas
Range	Formula
K6:L9	K6	=UNIQUE(C6:D10)
M6:M9	M6	=SUMIFS(F$6:F$10,$C$6:$C$10,K6,$D$6:$D$10,L6)
K12:M15	K12	=SORT(K6:M9,3,-1)
Dynamic array formulas.

 

[mach82]

Thx for this solution. I guess it would normally do the trick, though with tens of thousands of records it may not be very convenient to use. I need something more flexible, preferrably using dynamic arrays with a single formula.

 

[shaowu459]

Quite a longgggg formual....However, I assume Pivot Table with a dynamic data source is the best way for your case.

Book1.xlsx
	A	B	C	D	E	F	G	H
1	Department	Name	Sales Date	Sales Value		Department	Name	Sales Value
2	Finance	Garry miller	2019	60270		Finance	Garry miller	60270
3	Finance	Richard Elliot	2020	10000		Finance	Richard Elliot	30000
4	Finance	Richard Elliot	2019	20000
Sheet5

Cell Formulas
Range	Formula
F2:H3	F2	=SORT(IF({1,1,0},UNIQUE(CHOOSE({1,2},A2:A4,B2:B4)),TRANSPOSE(MMULT(TRANSPOSE(ROW(A2:A4)^0),(A2:A4&B2:B4=TRANSPOSE(INDEX(UNIQUE(CHOOSE({1,2},A2:A4,B2:B4)),,1)&INDEX(UNIQUE(CHOOSE({1,2},A2:A4,B2:B4)),,2)))*(D2:D4)))),3,-1)
Dynamic array formulas.

 

[CA_Punit]

Hi @shaowu459

Please help me with {1,1,0} which you have used with if logical.
What does it mean.

 

[mach82]

Thx shaowu459 - the formula works great I'm really impressed.

I still have to figure out on how it works. I was thinking on adding the filter formula in the future to filter out one of the departments. Is it possible?

 

[CA_Punit]

Hi @shaowu459

Please help me with {1,1,0} which you have used with if logical.
What does it mean.

Figured it Out.
I never thought of this. Applying Array constant with If was just blew my mind. I will definitely add this trick. Thanks @shaowu459

 

[CA_Punit]

I hope i figured something easy with an helper column.

Book1
	A	B	C	D	E	F	G	H	I	J
1
2							Shaowu Formulas
3	Department	Name	Helper Column	Sales Date	Sales Value		Department	Name	Sales Value
4	Finance	c	Finance,c	2019	60270		Finance	c	60270
5	Finance	a	Finance,a	2020	10000		Finance	b	20000
6	Finance	b	Finance,b	2019	20000		Finance	a	10000
7
8		My sheet
9		Department	Name	Sales Value
10		Finance	c	60270
11		Finance	b	20000
12		Finance	a	10000
13
14
Sheet1

Cell Formulas
Range	Formula
G4:I6	G4	=SORT(IF({1,1,0},UNIQUE(CHOOSE({1,2},A4:A6,B4:B6)),TRANSPOSE(MMULT(TRANSPOSE(ROW(A4:A6)^0),(A4:A6&B4:B6=TRANSPOSE(INDEX(UNIQUE(CHOOSE({1,2},A4:A6,B4:B6)),,1)&INDEX(UNIQUE(CHOOSE({1,2},A4:A6,B4:B6)),,2)))*(E4:E6)))),3,-1)
C4:C6	C4	=A4&","&B4
B10:D12	B10	=SORT(IF({1,1,0},UNIQUE(CHOOSE({1,2},A4:A6,B4:B6)),SUMIFS(E4:E6,C4:C6,UNIQUE(C4:C6))),3,-1)
Dynamic array formulas.

Please point out if there is some error. it will help me develop understanding the formula

 

[shaowu459]

Hi @shaowu459

Please help me with {1,1,0} which you have used with if logical.
What does it mean.

Hi, for time difference i just see your post. Yes, the constant array in IF usually used to creat an array with part of the second and third parameters.

 

[shaowu459]

Hi @shaowu459

Please help me with {1,1,0} which you have used with if logical.
What does it mean.

Yes, the result of formula is a single array, so you can use FILTER.