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.
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.