Function Problems

Use Three or More Sort Criteria with the Excel SORTBY Function

Camila MilosovichCamila Milosovich Nov 12, 2026 869 views

Question details

Use the Excel SORTBY function to sort one data range with three or more sort criteria.

Use Three or More Sort Criteria with the Excel SORTBY Function
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.
Before you start

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.

Solution 1Recommended

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.

1
Select an empty output cell

Click a blank cell where the sorted results should spill, such as P17. Ensure the cells below and to the right are empty.

2
Enter the SORTBY formula

Type =SORTBY(A17:G250,I17:I250,1,K17:K250,-1,M17:M250,1,N17:N250,-1) and press Enter.

3
Review the sort priority

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.

4
Add more criteria when needed

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.

Add each SORTBY range and order as an argument pair
How multiple criteria work: The first range is the primary criterion. The second range breaks ties in the first, the third range breaks ties in the first two, and so on.
Sort multi-column spreadsheet data

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. 1. Select the complete table: Click any cell in the table, or drag to select the full data range including its header row.
  2. 2. Open the Sort dialog: On the Data tab, click Sort to open the multi-level sorting options.
  3. 3. Set the first sorting level: Choose the primary column under Column, then select Ascending or Descending under Order.
  4. 4. Add additional sort levels: Click Add Level and select each next column and order in the priority you want.
  5. 5. Apply the sort: Click OK to sort the table using all configured criteria.
Add multiple sort levels to control the order of tied records.Choose ascending or descending order for each selected column.Open and edit Microsoft Excel .xlsx and .xls files with format compatibility.Use a lightweight, familiar spreadsheet interface for everyday data management.
microsoft office alternative - wps office

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.