The SORTN function returns the first ‘n’ rows in a data set after performing a sort.
Syntax=SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], ...)range – The data set in which to sort and find the first n items. n – [OPTIONAL – 1 by default] The number of items to return. Must be greater than 0.display_ties_mode – [OPTIONAL – 0 by default] A number representing 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 identical to the nth row.
2: Show at most the first n rows after removing duplicate rows.
3: Show the first n unique rows at most, but show every duplicate of these rows.sort_column1 – [OPTIONAL] – The column’s index in range or a range outside of content containing the values to sort by.is_ascending1 – [OPTIONAL] – TRUE or FALSE. Indicates how to sort sort_column1. TRUE sorts in ascending order. FALSE in descending order.sort_column2, is_ascending2, ... – [OPTIONAL] – Additional columns and sort order flags used if a tie happens, in order of precedence
Sample Usages=SORTN(A1:A10, 2)
//Sorts the values in the range A1:A10, and returns the first two items.=SORTN(A2:C20, 2, 2, B2:B20, TRUE)
//Sorts the items in the range A2:C20, returns the first two items after removing the duplicates, then and sorts the range by column in ascending order.
What is the SORTN Function in Google Sheets?
The simple definition of the SORTN Function in Google Sheets is that the SORTN Function returns the first ‘n’ rows in a data set after performing a sort. Here, the data set is sorted either in increasing or decreasing order. So, you can filter out the highest or lowest N number of items from a dataset.
Why use the SORTN Function in Google Sheets?
As described above, the SORTN function Google Sheets is helpful when you want to sort a range of data and return only a selected number of items from the sorted result. So let’s say you have a list of grade sheets of students. You want to choose the top 10 highest scoring students for awarding scholarships. You can perform this task by using the SORTN function in Google Sheets with minimum effort.
How to use the SORTN Function in Google Sheets?
Sample Examples of the SORTN Function in Google Sheets
=SORTN(A1:A10, 2)Here, the range selected is A1:A10, and you want the first two items.
=SORTN(A2:C20, 2, 2, B2:B20, TRUE)Here, the range is A2:C20, and you want the first two items after removing duplicate items. Also, you want to sort the range by column B in ascending order.
=SORTN(A2:C20, 2, 3, B2:B20, TRUE, 3, FALSE)Here, the range is A2:C20, and you want the first two items after removing duplicate items. Also, you want to sort the range by column B in ascending order and then by column number 3 in descending order.
Important Note:
- range is sorted only by the specified columns. Other columns are returned in the order they originally appeared.
- If sort_column1 and is_ascending1 are omitted, the sort is performed on the lowest-index column in the range, with subsequent columns used to sort if there are ties.
Example 1: SORTN function in Google Sheets on a range
- Let’s say you have a grade sheet of a class of students. You want to award scholarships to the top 5 students.
- Use the following formula:
=SORTN(A2:B17,5,0,B2:B17,FALSE)- Here, the first parameter (A2:B17) is the range of students and their individual marks.
- The second parameter (5) means you want to select the first five rows.
- The third parameter (0) means you are okay with having duplicates, as there is a possibility that two students have the same marks.
- The fourth parameter (B2: B17) is a column of marks. This means that we want to sort the range by this column. The data set will be sorted based on the marks scored by individual students.
- The last parameter (FALSE) means you want the sorted data to be in descending order. As we want to identify the top 5 highest scoring students, we set the last parameter to false.
- The output will be the top 5 students with the highest marks, as shown below:
Example 2: SORTN function in Google Sheets on a range and sort by multiple columns
- Let’s say you have a grade sheet of a class of students. You want to award scholarships to the top 7 students. If the marks are the same, you want younger students to get opportunities over older ones.
- Use the following formula:
=SORTN(A2:C17,7,0,B2:B17,FALSE,C2:C17,TRUE)- Here, the first parameter (A2:C17)is the range of students and their individual marks.
- The second parameter (7) means you want to select the first seven rows.
- The third parameter (0) means you are okay with having duplicates, as there is a possibility that two students have the same marks.
- The fourth parameter (B2:B17) is a column of marks. This means that you want to sort the range by this column. The data set will be sorted based on the marks scored by individual students.
- The fifth parameter (FALSE) means you want the sorted data to be in descending order. As we want to identify the top 5 highest scoring students, we set the fifth parameter to false.
- The sixth parameter (C2:C17) is a column of age. This means that you want to sort the range by this column if the marks of two students are the same. So if X and Y have the same marks, X gets the priority if X is younger than Y.
- The last parameter (TRUE) means you want to sort the dataset in ascending order of age. So if X is younger than Y with the same marks, then X is higher in the pecking order.
- The output will be the top 7 students with the highest marks, as shown below:
Conclusion
After sorting the dataset, you can use the SORTN function to extract the N number of items. You can also sort the data based on multiple columns. Hope this guide on sortn google sheets helped you. If you want to know more about such topics, go through the documentation here. You can learn about sorting in Google Sheets by clicking here.
See Also
Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!
Filter Data by Color in Google Sheets: In this article, we would have a look at how to Filter Data by Color in Google Sheets.
Google Sheets: How to Use Pivot Tables: Learning how to create and use Pivot Tables for analysing data in Google Sheets.
Complete Guide on the REGEXEXTRACT Function in Google Sheets for 2022: Learn how to use the REGEXEXTRACT function in Google Sheets and its variations.