Forum Discussion
How to sort the unique() values by their sumifs()
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.
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 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.
- You're welcome! Glad the 'horsepower' has been restored to your Excel.
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.
This workaround is good for some basic stacking:
=SORT(IF({1,0},UNIQUE(A3:A7),SUMIFS(B3:B7,A3:A7,UNIQUE(A3:A7))),2,-1)- This is helpful thank you.
