SORTBY Function in Excel - Dynamic Array Function
The SORTBY Function is one of six new dynamic array functions in Microsoft Excel. SORTBY function allows you to sort one range, based on another range. Example: You have a list of employees with the following fields: Name, Department, and Salary. You want to sort it on a separate workbook or file, but those users don't need to see salary. You wish to sort by department in ascending order and then salary in descending order. You can do that with the SORTBY function. You are using Salary as a sort field but other users can't see salary.
SORTBY Syntax
> SORTBY (array, by_array, [sort_order], [array/order], ...)
SORTBY Syntax Example
> SORTBY(A2:B10,C2:C10,-1)
SORTBY Arguments
array - Range or array to sort.
by_array - Range or array to sort by.
sort_order - [optional] Sort order. 1 = ascending (default), -1 = descending.
array/order - [optional] Additional array and sort order pairs.
Example 1 of SORTBY
In this first example, I have a range from A1 to C10 showing employee's name, department and salary. I want to sort and only display the names and department, but I want to sort by salary in descending order. In the results, salary will not be shown, but it is the sort order. To accomplish this, I'll use the SORTBY function.
The formula in cell G2 shows A2 to B10 as the range, followed by the sort column which is salary, C2 to C10. In the formula, the negative 1 means descending order. A positive one would be ascending.
I did this on the same worksheet, but I could have done this on another worksheet or workbook.
Example 2 of SORTBY
With SORTBY, you can sort by multiple columns. I have an Excel table named Reps with the four fields: Rep, Customer, State, and Revenue in A2 to D14. I want to sort by state in ascending order then by revenue in descending order, but revenue will not show in the sorted data. To accomplish this, I used SORTBY in cell O2.
The formula is =SORTBY(Reps\[\[Rep\]:\[State\]\],Reps\[State\],1,Reps\[Revenue\],-1)
The first argument tells you what I'm sorting: I have a table Reps Table and the fields Rep through State. The second argument is the first sort priority which is State, and it is ascending. Notice the number 1 after it. The final argument is the next sort priority which is Revenue and it is descending which is a -1 (negative 1).
SORTBY Function in Excel - Dynamic Array Function by Chris Menard
