SheetFX
≡
<< Go back to all functions

SORTN

The SORTN function in Google Sheets returns the first n items in a data set after performing a sort. It is a useful tool for filtering and organizing data based on specific criteria.

Function Syntax and Parameters

Syntax: SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], [...])

Parameters:

  • range: The range of cells or a data array to be sorted.
  • n: [Optional] The number of items to return. If omitted, n will default to 1.
  • display_ties_mode: [Optional] Specifies how ties are handled when two or more items have the same sort values. Possible values are "ALL", "DISPLAY_FIRST", "DISPLAY_LAST", and "CUSTOM".
  • sort_column1, is_ascending1: [Optional] Specifies the first sort column and its sorting order. Additional sort columns can be added as parameters.

Step-by-Step Tutorial

  1. Basic usage of SORTN:

    • Example: =SORTN(A1:B5, 3)
    • Result: Returns the first 3 items from the range A1 to B5 after sorting them.

  2. Sorting with multiple columns and custom tie-breaking rules:

    • Example: =SORTN(A1:B5, 2, "CUSTOM", 1, TRUE, 2, FALSE)
    • Result: Sorts the range A1 to B5 based on the values in sort_column1 (ascending order) and sort_column2 (descending order) while customizing how ties are displayed.

Use Cases and Scenarios

  1. Top N Sales: Retrieve the top N items from a sales dataset based on the highest sales figures.
  2. Ranking Players: Determine the highest-scoring players in a sports tournament.
  3. Budget Analysis: Find the top N expenses or income sources in a budget sheet.

Related Functions

  • SORT: Sort a range of cells or a data array.
  • LARGE: Get the nth largest value from a range.
  • SMALL: Get the nth smallest value from a range.

Related Articles