# SORTN Function in Google Sheets

The SORTN function lets you sort a range of data and return the first*n*items from that range. The function creates a new range of data with the new, sorted output from the function. SORTN can significantly reduce the number of steps that it takes to get the data that you need out of a table.

SORTN is similar toFILTER, which filters a specified range, but SORTN returns the highest n amounts while FILTER returns the matching amounts.

Excel, the most popular spreadsheet program, does not have this function.

## Purpose

Returns*n*number of rows from a range of data.

## Video explanation

## Syntax

`=SORTN(range,[n],[display_ties_mode],[sort_column1,is_acending1], ...)`

`range`

- The entire range of data that is to be sorted`n`

- The number of items to return`display_ties_mode [OPTIONAL]`

- An*optional*number designating the way to display ties.- 0: Show at most the first n rows in the sorted range.
- 1: Show at most the first n rows, plus any additional rows that are identical to the nth row.
- 2: Show at most the first n rows after removing duplicate rows.
- 3: Show at most the first n unique rows, but show every duplicate of these rows.

`sort_column1 [OPTIONAL]`

- The column of data that contains the criteria that you are sorting. Can only be one column at a time. If this value is not specified, the lowest index column in the range will be used and subsequent columns will be used if a tie-breaker is needed.`is_ascending1 [OPTIONAL]`

- Two possible answers - TRUE for ascending (1,2,3), FALSE for descending (3,2,1)`sort_column2 - [OPTIONAL]`

- Add as many of these as you want. On and on....

Tip: When using SORTN, start your formula in a cell that should be the upper left most cell for the output. This function writes the output in, below, and to the right of your starting point. The formula text will remain in the upper left cell and can be changed.

Tip: If sort_column1 and is_ascending1 aren't included, the sort is performed on the lowest-index column in range, with subsequent columns used to sort if there are ties.

## Examples

### Example 1

Using a simple SORTN function with one column of data and extracting the two highest values. The display_ties is set to 0 which means show two highest values regardless of ties. The zero isn't specified, but it is the default. This is the same as typing "0".

A | |

1 | 2 |

2 | 5 |

3 | 3 |

4 | 3 |

5 | `=SORTN(A1:A4,2)` |

6 |

A | |

1 | 2 |

2 | 5 |

3 | 3 |

4 | 3 |

5 | 2 |

6 | 3 |

### Example 2

The display_ties parameter has been changed to "1" in this example which tells Sheets to show the two highest values including any ties. The two first values are both 2, and it has included all three occurrences of 2.

A | |

1 | 2 |

2 | 2 |

3 | 2 |

4 | 5 |

5 | 3 |

6 | 3 |

7 | `=SORTN(A1:A6,2,1)` |

8 | |

9 |

A | |

1 | 2 |

2 | 2 |

3 | 2 |

4 | 5 |

5 | 3 |

6 | 3 |

7 | 2 |

8 | 2 |

9 | 2 |

### Example 3

The display_ties parameter is set to 2 which means show at most only 2 rows but don't show any duplicates

A | |

1 | 2 |

2 | 2 |

3 | 2 |

4 | 5 |

5 | 3 |

6 | 3 |

7 | `=SORTN(A1:A6,2,2)` |

8 |

A | |

1 | 2 |

2 | 2 |

3 | 2 |

4 | 5 |

5 | 3 |

6 | 3 |

7 | 2 |

8 | 3 |

### Example 4

The display_ties parameter is set to 3 which means show at most only 2 items but show all duplicates of these items. 2 and 3 are the two highest items so it is showing all 2s and 3s no matter how many there are. Note: It seems to me that this function should also be showing all of the threes, but it is not. I submitted this as a question on the Google Docs forum and will update this notes with what I find out.

**Alert!**This function, as of early September 2017, is not working properly. If you look at theshared Sheet, starting at row 33, the function is not returning all of the 3's that it should. The bug has been reported to Googlehere.

A | |

1 | 2 |

2 | 2 |

3 | 2 |

4 | 5 |

5 | 3 |

6 | 3 |

7 | `=SORTN(A1:A6,2,3)` |

8 | |

9 | |

10 | |

11 |

A | |

1 | 2 |

2 | 2 |

3 | 2 |

4 | 5 |

5 | 3 |

6 | 3 |

7 | 2 |

8 | 2 |

9 | 2 |

10 | 3 |

11 | 3 |

### Example 5

Two column SORTN, only sorted one column.

A | B | |

1 | Hen | Brown |

2 | Sheep | White |

3 | Bull | brown |

4 | Goat | White |

5 | `=SORTN(A1:A4,2,1)` | |

6 |

A | B | |

1 | Hen | brown |

2 | Sheep | White |

3 | Bull | brown |

4 | Goat | white |

5 | Bull | |

6 | Goat |

### Example 6

Two column SORTN, specifying both columns in the range.

A | B | |

1 | Hen | Brown |

2 | Sheep | White |

3 | Bull | brown |

4 | Goat | White |

5 | `=SORTN(A1:B4,2,1)` | |

6 |

A | B | |

1 | Hen | brown |

2 | Sheep | White |

3 | Bull | brown |

4 | Goat | white |

5 | Bull | brown |

6 | Goat | white |

### Live examples in Sheets

Go tothis spreadsheeetfor several examples of the SORTN function that you can study and use anywhere you would like.

- Details