INDEX
About the SORTN Function
Overview of SORTN
| Sort Data and Extract the Top N EntriesGoogle Sheets Function | ||
|
=SORTN( range, N, [display_option], [sort_column1], [is_ascending1], … ) Summary The SORTN function sorts a specified range and returns the top N entries. |
||
|
Explanation of Arguments
- Display Option: Specifies the type of data to return.
- 0: Allows duplicates (default).
- 1: Removes duplicates.
- Sort Order: Specifies the order for sorting the columns.
- TRUE: Ascending order (smallest to largest).
- FALSE: Descending order (largest to smallest).
When to Use SORTN
- To extract only the top N entries from a dataset.
- To sort data in ranking order.
- To retrieve unique values without duplicates.
How to Use SORTN
The following table demonstrates the basic usage of the SORTN function.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Score | Age |
| 2 | John | 90 | 25 |
| 3 | Jack | 85 | 30 |
| 4 | Smith | 95 | 22 |
| 5 | Tom | 85 | 28 |
| 6 | =SORTN(A2:C5, 2, 0, 2, FALSE) |
Result: Extract Top 2 Scores
| A | B | C | |
|---|---|---|---|
| 6 | Smith | 95 | 22 |
| 7 | John | 90 | 25 |
Example 2: Sort by Score, Tie-Break by Age
=SORTN(A2:C5, 3, 0, 2, FALSE, 3, TRUE)
| A | B | C | |
|---|---|---|---|
| 6 | Smith | 95 | 22 |
| 7 | John | 90 | 25 |
| 8 | Tom | 85 | 28 |
Points to Note
- Empty cells in the data range can affect the results.
- If Display Option is set to
0(default), duplicates are allowed. - If Display Option is set to
1, duplicate values are removed.
Conclusion
- The SORTN function is a powerful tool for sorting and extracting the top N entries from a dataset.
- It allows for flexible data manipulation by enabling sorting based on multiple columns.
- Perfect for ranking and efficient data extraction tasks.