Forum Discussion
yennek
Dec 11, 2022Copper Contributor
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.
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_HousamiCopper 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?
- Wasel_HousamiCopper 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?
- yennekCopper 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.
- Patrick2788Silver ContributorYou're welcome! Glad the 'horsepower' has been restored to your Excel.
- yennekCopper Contributor
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.