Explanation
In the example shown, we want to sort data in B5:D14 first by group in descending order. Here is the configuration needed:
- array = B5:D14
- by_array1 = D5:D14
- sort_order1 = 1
The formula below will sort data by group A-Z:
=SORTBY(B5:D14,D5:D14,1) // sort by group only
To extend the formula to sort next by score, in descending order, we need to add:
- by_array2 = C5:C14
- sort_order2 = -1
With these arguments added, the complete formula is:
=SORTBY(B5:D14,D5:D14,1,C5:C14,-1)
Ascending vs. Descending
Data is sorted in ascending order (A-Z) by default. This behavior is controlled by the sort_order arguments. Sort_order can be set to 1 (ascending) or -1 (descending). The formula below is the same as above except that it sorts scores in ascending order:
=SORTBY(B5:D14,D5:D14,1,C5:C14,1)
Dynamic Array Formulas are available in Office 365 only.
Related formulas
Sort by one column
The SORT function requires very little configuration. In the example shown, we want to sort data in B5:D14 by the third column, Group. For array , we provide entire range, B5:D14. For sort_index , we provide 3: =SORT(B5:D14,3) With this formula in F5, the SORT function outputs the sorted array in...
Related functions
SORTBY Function
The Excel SORTBY function sorts the contents of a range or array based on the values from another range or array. The range or array used to sort does not need to appear in results.
