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.

 

[RICH937]

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.

This is an old post, but the solution absolutely saved my bacon. Working on one of the most complex business models I've made, and trying to use dynamic arrays wherever possible. Without this, I very likely would have had to backtrack a week's work, and been back to updating every time a new customer/transaction was added. I greatly appreciate it!

 

[shaowu459]

This is an old post, but the solution absolutely saved my bacon. Working on one of the most complex business models I've made, and trying to use dynamic arrays wherever possible. Without this, I very likely would have had to backtrack a week's work, and been back to updating every time a new customer/transaction was added. I greatly appreciate it!

I'm glad the formula is useful

now, there is better solution for Microsoft 365:

Book1
	A	B	C	D	E	F	G	H	I
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
5
6
Sheet1

Cell Formulas
Range	Formula
F2:H3	F2	=UNIQUE(HSTACK(A2:B4,SUMIFS(D:D,A:A,A2:A4,B:B,B2:B4)))
Dynamic array formulas.

 

[RICH937]

I'm glad the formula is useful

now, there is better solution for Microsoft 365:

Book1
	A	B	C	D	E	F	G	H	I
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
5
6
Sheet1

Cell Formulas
Range	Formula
F2:H3	F2	=UNIQUE(HSTACK(A2:B4,SUMIFS(D:D,A:A,A2:A4,B:B,B2:B4)))
Dynamic array formulas.

Saved me again! The model I'm building has ~30K transactions made by ~10K customers in ~120 categories/channels. The longer version worked great when I was only exploring results from 1 channel, but when I decided to look at all I immediately got the dreaded "Excel ran out of resources" error. I plugged in the shorter version, and knock on wood, I haven't gotten the error since! Because I'm building in dynamic arrays, I edited it to:
=SORT(SORT(UNIQUE(HSTACK(CHOOSECOLS(DK4#,1,3,2),SUMIFS(DN:DN,DK:DK,CHOOSECOLS(DK4#,1),DM:DM,CHOOSECOLS(DK4#,3),DL:DL,CHOOSECOLS(DK4#,2)))),4,-1),1,1)
The only thing I can't figure out is how to eliminate the full column lookups. Doesn't seem to affect anything in the calculation, so I'm running with it.

thanks again shaowu459. You are a rockstar!

 

[Alex Blakenburg]

The only thing I can't figure out is how to eliminate the full column lookups. Doesn't seem to affect anything in the calculation, so I'm running with it.

Your full column lookups are inside of the SumIfs function. SumIf per the MS document "recognize the last-used row" and using whole column referencing in that way should not impact performance.
I have not found a definitive reference that SumIfs is the same but since it is said to be more efficient I imagine that it would have at least inherited that feature.