Our Service

SORTN

In this comprehensive guide, we will explore the SORTN function in Google Sheets, which is a powerful formula that allows you to sort and filter data based on specific criteria. The SORTN function is particularly useful when you want to display a limited number of top or bottom values from a dataset. We will cover the syntax, examples, tips and tricks, common mistakes, troubleshooting, and related formulae for the SORTN function.

SORTN Syntax

The SORTN function in Google Sheets has the following syntax:

=SORTN(range, [n], [display_ties_mode], [sort_column1, is_ascending1], [sort_column2, is_ascending2], …)

Here’s a breakdown of the arguments:

  • range: The range of cells you want to sort and filter.
  • n (optional): The number of top or bottom values you want to display. If omitted, it defaults to 1.
  • display_ties_mode (optional): A number that determines how to handle ties. If set to 0 (default), SORTN will display only the top n values. If set to 1, SORTN will display all tied values, even if it exceeds the specified n value.
  • sort_column1, is_ascending1 (optional): The first column to sort by and a boolean value (TRUE or FALSE) indicating whether to sort in ascending order. If omitted, the first column of the range is used, and the default sorting order is ascending.
  • sort_column2, is_ascending2 (optional): Additional columns to sort by and their corresponding boolean values for ascending order. You can add as many additional sorting columns as needed.

SORTN Examples

Let’s look at some examples of how to use the SORTN function in Google Sheets:

Example 1: Basic usage of SORTN to display the top 3 values in a range.

=SORTN(A1:A10, 3)

This formula will return the top 3 values from the range A1:A10, sorted in ascending order.

Example 2: Using SORTN to display the top 5 values, including ties.

=SORTN(B1:B20, 5, 1)

This formula will return the top 5 values from the range B1:B20, including any tied values, sorted in ascending order.

Example 3: Using SORTN with multiple sorting columns and custom sorting orders.

=SORTN(A1:C10, 4, 0, 2, FALSE, 3, TRUE)

This formula will return the top 4 rows from the range A1:C10, sorted first by column B in descending order, and then by column C in ascending order.

SORTN Tips & Tricks

  • Use the SORTN function in combination with other functions like QUERY or FILTER to further refine your results.
  • If you want to display the bottom n values instead of the top n values, simply set the is_ascending argument to the opposite value (e.g., TRUE for descending order).
  • Remember that the display_ties_mode argument can be useful when you want to include all tied values in your results, even if it exceeds the specified n value.
  • When using multiple sorting columns, make sure to provide the correct column index and sorting order for each column.

Common Mistakes When Using SORTN

  • Forgetting to include the range argument, which is required for the SORTN function to work.
  • Using incorrect column indexes when specifying multiple sorting columns. Remember that the column index is relative to the range, not the entire sheet.
  • Not specifying the correct boolean value for the is_ascending argument, which can lead to unexpected sorting orders.
  • Using the wrong display_ties_mode value, which can cause the formula to return more or fewer results than expected.

Why Isn’t My SORTN Working?

If your SORTN function isn’t working as expected, consider the following troubleshooting steps:

  • Double-check the range argument to ensure it covers the correct data.
  • Verify the n value to make sure it’s set to the desired number of top or bottom values.
  • Check the display_ties_mode argument to ensure it’s set to the correct value for your desired output.
  • Review the sort_column and is_ascending arguments to confirm they are set correctly for your desired sorting order.
  • Ensure there are no errors in the data range that could be causing the SORTN function to return incorrect results.

SORTN: Related Formulae

Here are some related formulae that can be used in conjunction with or as alternatives to the SORTN function in Google Sheets:

  • SORT: Sorts a range of cells based on specified sorting columns and orders.
  • RANK: Returns the rank of a specific value within a dataset.
  • QUERY: Runs a Google Visualization API Query Language query across a range of cells and returns the results.
  • FILTER: Filters a range of cells based on specified conditions.
  • UNIQUE: Returns unique values from a range of cells, removing duplicates.

By mastering the SORTN function and understanding its related formulae, you can effectively sort and filter data in Google Sheets to display the most relevant information for your needs.

Related

Did you find this article useful?

Share it with your friends or colleagues

About Aepoch Advisors

We are a boutique accounting and consulting firm servicing international businesses operating in China. We offers book keeping and business advisory service. We also help our clients select and implement SAAS business applications. 
 
Cloud technology significantly reduces cost foreign companies traditionally spent on tax compliance and ERP systems. Our cloud professionals can help with streamlining your management and controling structure, as well as advising you on how to reduce risks and maximize profits with software purposely built for Chinese business. Contact us today to learn more about our services.
Learn More