Forum Discussion

yennek's avatar
yennek
Copper Contributor
Dec 11, 2022
Solved

How to sort the unique() values by their sumifs()

From a large data set, I’m generating a list of unique names with =UNIQUE(...). Next to those unique names there’s a simple =SUMIFS(...) giving the totals. All is good. I can use =Sort(Unique(...)) t...
excel
Formulas and Functions
office 365
  • Harun24HR's avatar
    Harun24HR
    Dec 12, 2022

    yennek All you can do in a single formula. Try the following formula.

    =SORT(HSTACK(UNIQUE(A3:A7),SUMIFS(B3:B7,A3:A7,UNIQUE(A3:A7))),2,-1)

     See the attached file.

     

yennek's avatar
yennek
Copper Contributor
Dec 14, 2022

Harun24HR Patrick2788 This has been a very, very helpful discussion. Thank you. Microsoft has returned my HSTACK() function. So, I now have a single function, a mile long, decipherable by no one, that creates an array 13 columns wide with many variations of sumifs, countifs, etc, etc. To sort by any of the 13 columns requires entering a 1, -1 above any of the columns and then =sort(hstack(unique(filter(.... figures it out. Somewhat miraculous, but me thinks I should go back and rethink how the whole house is constructed.  Thanks again.

Patrick2788's avatar
Patrick2788
Silver Contributor
Dec 14, 2022
You're welcome! Glad the 'horsepower' has been restored to your Excel.

Resources