SORT with UNIQUE, but not on UNIQUE column?

[slam]

Sorry of that title is confusing.

I have this formula in A15:

=UNIQUE(FILTER(G2:G400, G2:G400<>""))

Then in B15, I have this formula:

=SUMIF(G:G,A15,H:H)/COUNTIF(G:G,A15)

I want to sort this, but by column B, not by column A. Is this possible when using a UNIQUE array? I couldn't do it with the built in Sort functionality, but perhaps there's a way to add SORT to the formula?

Thanks

 

[Fluff]

How about in A15 only

=LET(u,UNIQUE(FILTER(G2:G400,G2:G400<>"")),SORT(HSTACK(u,AVERAGEIFS(H:H,G:G,u)),2))

 

[slam]

Hi Fluff,

Thanks for the reply. I'm assuming that's an array formula, but when I enter it, its showing each value from column G many more times than it actually occurs. Incidentally, it's filling down to the correct number of rows that match the number of unique values from column G, but it's only showing 4 of the unique values (repeated many times) whereas there's actually 64 unique values.

If it's relevant, this is in Excel 365, and the formula in column G2 is:

=IFERROR(VLOOKUP(F2,Attributes!A:C,3,FALSE),"")

Thanks

 

[Fluff]

Did you just put the formula in A15 ONLY?

 

[slam]

I tried that, but it doesn't auto populate the other cells; I dragged it down, so maybe that was one mistake.

 

[Fluff]

Did you enter it with Ctrl Shift Enter? If so don't, just use Enter.

 

[slam]

I tried that also, but it gives me a #SPILL! error

 

[Fluff]

Are you trying to put the formula into a Table, or just a normal range?

 

[slam]

Okay, that clued me in; perhaps it was a range from something I just did previously, so I started from scratch, and now it populated with a normal Enter. I reversed the sort order with a -1 at the end of the formula, and now it's perfect. Thank you so much for your help and patience!

 

[Fluff]

You're welcome & thanks for the feedback.