Forum Discussion

yennek's avatar
yennek
Copper Contributor
Dec 11, 2022

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(...)) to sort by the unique name, but how can I sort by the totals in the sumifs column. Thought it would easy; tried searching the forum, tried sortby(), but can’t get it. Would prefer not to have to introduce pivot tables or the table functionality. Thanks. 

 

  • Wasel_Housami's avatar
    Wasel_Housami
    Copper Contributor

    WONDERFUL THANK YOU

    I have two questions

    1- can I get the result of that values bigger than 2?

    2- how to do a conditional formatting for the exact resalt table without selecting too many cells?

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor

    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.

     

    • Wasel_Housami's avatar
      Wasel_Housami
      Copper Contributor

      WONDERFUL THANK YOU

      I have two questions

      1- can I get the result of that values bigger than 2?

      2- how to do a conditional formatting for the exact resalt table without selecting too many cells?

    • yennek's avatar
      yennek
      Copper Contributor

      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
        You're welcome! Glad the 'horsepower' has been restored to your Excel.
    • yennek's avatar
      yennek
      Copper Contributor

      Harun24HR 

      WONDERFUL THANK YOU! Would never have gotten that on my on. 
      I actually have more countifs, sumifs, averageifs, going out to the right and will try to incorporate those into the array. But first...

       

      My 365 Subscription (on Mac) does not have the HSTACK function!!! (Infuriating because I just jumped through hoops with Microsoft earlier this week to convert to 365 so I could get the Unique and Filter functions. )

       

      Many Excel functions can be rebuilt through formulas that do what the function does. I wonder if HSTACK... thanks again. 

      • Harun24HR's avatar
        Harun24HR
        Bronze Contributor

        yennek CHOOSE() could be good alternative of HSTACK(). Try this one.

        =SORT(CHOOSE({1,2},UNIQUE(A3:A7),SUMIFS(B3:B7,A3:A7,UNIQUE(A3:A7))),2,-1)

Resources