The SORTBY function is useful in cases where you want to sort a range or array by some key column or row that is not part of the array or range. This function is thus more flexible than the SORT function, as the "by_array" can be any range or array of the appropriate size.

You can find the examples in this blog for SORTBY in this Excel workbook here.

See Spread in Action

Get the Latest Version of Spread Today

Download now!

SORYBY Syntax

SORTBY(array,by array1,[order array1]
array Array or range to be sorted (required).
by array1 array or range of first sort key (required).
order array1 Optional -1 for descending (default is 1, ascending).
by array2 Optional array or range of second sort key
order array2 -1 for descending, 1 for ascending (required if by array2 specified).
This function can accept additional arguments in pairs, specifying the next sort key range and order.

The examples below use this SoryByData table:

image1

The first example sorts on the ProductName column in the default ascending order:

=SORTBY(SortByData,SortByData[ProductName])

image2

Note that the above result in J14:O24 is formatted to appear like a table, but is not actually a table, as dynamic array formulas and spilling behavior are not supported in table cells. Instead, the range is formatted to appear like a table and the table header cells J13:O13 are copied from the original table header cells B13:G13.

The next example sorts on the Quantity in descending order:

=SORTBY(SortByData,SortByData[Quantity],-1)

image3

The last example sorts on the SalesPerson in ascending order, then Quantity in descending order:

=SORTBY(SortByData,SortByData[SalesPerson],1,SortByData[Quantity],-1)

image4

Note that instead of specifying column or row indices in the range for sort keys, when using the SORTBY function you can instead use structured table references to the entire table column, which is much clearer and easier to read than the formulas using the SORT function.

Also note that the specified array or range of values does not need to be part of the sort data, it only needs to have the correct dimensions and specify a set of values for sorting, which can be calculated values. Later blogs will explore this more using other dynamic array functions with SORTBY.

SORTBY in GrapeCity Products

The SORTBY function is supported in these GrapeCity spreadsheet controls and components:

See Spread in Action

Get the Latest Version of Spread Today

Download now!