The SORTN function in Google Sheets is a powerful tool designed to help users extract a specified number of entries from a dataset after sorting it. This function is particularly useful when you wish to analyze or present only the top items based on specific criteria such as sales figures, grades, or any other sortable values.

Syntax

SORTN(range, [n], [display_ties], [sort_column], [sort_order])

• range: The dataset you want to analyze or sort.
• n: The number of items to return from the sorted dataset.
• display_ties (optional): A boolean value (TRUE or FALSE) that indicates whether to display tied items.
• sort_column (optional): The index of the column used for sorting if the range has multiple columns.
• sort_order (optional): Determines the order of sorting; 1 for ascending or 0 for descending.

Example #1

SORTN(A2:B10, 5, FALSE, 2, 0)

This function retrieves the top 5 entries from the range A2:B10 based on the values in column B in descending order. For instance, if column B contains sales numbers, it will return the highest sales figures along with their corresponding data from column A.

Example #2

SORTN(A1:D10, 3, TRUE, 1, 1)

In this example, the top 3 entries from the range A1:D10 are extracted based on the sorting of column A in ascending order, displaying tied entries. This could be used to list the three lowest scores in a class.

Example #3

SORTN(E1:E20, 10)

This function fetches the first 10 unique entries from the range E1:E20 sorted in ascending order. This is useful for quickly identifying the top 10 products sold.

Error handling

• N/A: Indicates that the function cannot find any items to return, likely because the range is empty.
• VALUE!: Occurs when a non-numeric value is provided for the ‘n’ parameter.
• REF!: Happens if the specified range refers to a non-existent cell or range.

Conclusion

The SORTN function is an essential tool in Google Sheets for anyone needing to filter and sort datasets efficiently. By allowing users to extract the top n items following a sort, it enhances data analysis capabilities significantly. Whether you’re handling sales data, academic scores, or other types of numeric information, mastering this function can greatly streamline your data management processes.