Use Three or More Sort Criteria with the Excel SORTBY Function
Question details
Use the Excel SORTBY function to sort one data range with three or more sort criteria.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to sort a table by several columns in a defined priority order.
- Observed behavior
- SORTBY evaluates the first sort criterion first, then uses each following criterion to order rows that have matching values in the preceding criterion.
Make sure the data range and every sort-by range contain the same number of rows. Use 1 for ascending order and -1 for descending order for each sort criterion.
Add each SORTBY range and order as an argument pair
Use this method when you need to sort a single table by three, four, or more columns without manually sorting the source data.
After the array to return, enter every sort range followed immediately by its corresponding sort order. Excel applies the criteria from left to right, using later criteria only when earlier values are the same.
Click a blank cell where the sorted results should spill, such as P17. Ensure the cells below and to the right are empty.
Type =SORTBY(A17:G250,I17:I250,1,K17:K250,-1,M17:M250,1,N17:N250,-1) and press Enter.
In the formula, Excel sorts A17:G250 by I17:I250 in ascending order, then K17:K250 in descending order, M17:M250 in ascending order, and N17:N250 in descending order.
Before the final closing parenthesis, add another sort range and order pair, such as ,O17:O250,1. Keep every sort range aligned with the returned array.

Sort Data by Multiple Criteria in WPS Spreadsheets
WPS Spreadsheets lets you organize a table by several columns with the Sort dialog. Select the table, add levels for each column, and choose ascending or descending order for every level.
- 1. Select the complete table: Click any cell in the table, or drag to select the full data range including its header row.
- 2. Open the Sort dialog: On the Data tab, click Sort to open the multi-level sorting options.
- 3. Set the first sorting level: Choose the primary column under Column, then select Ascending or Descending under Order.
- 4. Add additional sort levels: Click Add Level and select each next column and order in the priority you want.
- 5. Apply the sort: Click OK to sort the table using all configured criteria.

Frequently Asked Questions
Can SORTBY use more than two sort criteria?
Yes. After the array argument, add as many sort-by range and sort-order pairs as needed. For example, use ,I17:I250,1,K17:K250,-1,M17:M250,1 for three criteria.
What do 1 and -1 mean in a SORTBY formula?
Use 1 for ascending order, such as A to Z or smallest to largest. Use -1 for descending order, such as Z to A or largest to smallest.
Why does my SORTBY formula return a #VALUE! error?
Check that every sort-by range has the same row count as the array being returned. For example, if A17:G250 is the array, I17:I250 and every other criterion range must also run from row 17 through row 250.
Which SORTBY criterion has the highest priority?
The first sort-by range and order after the array have the highest priority. Each later range is used only to sort rows that are tied under all earlier criteria.
Can I sort an Excel table with multiple criteria without a formula?
Yes. Select the table, go to the Data tab, click Sort, and use Add Level to define each column and its order. This sorts the original data instead of creating a spilled result range.




