Find Top N Values with Criteria in Google Sheets (MAX/LARGE)

Published on

The most flexible way to find the top N values with criteria in Google Sheets is by combining SORTN and FILTER. The MAX or LARGE functions alone don’t support criteria, and while MAXIFS can return the maximum value based on criteria, it doesn’t return the top N values.

To extract the top N values with criteria, we use FILTER to apply the condition and SORTN to select the highest values from the filtered data.

SORTN also provides four tie-handling modes, allowing you to control how duplicates are treated when multiple values share the same rank.

Below, we’ll explore four different formulas, so you can choose the one that fits your needs.

Sample Data

The sample dataset contains total sales for seven products across different months. Suppose we need to find the top three sales values for January.

Example dataset to Find Top N Values with Criteria in Google Sheets

The criteria will be “Jan” in Column A.

Generic Formula

Use the following formula structure to find the top N values with criteria:

=SORTN(FILTER(data, criteria), n, ties_mode, index, FALSE)

Where:

  • data: The dataset from which to extract the top N values.
  • criteria: The condition that filters relevant data.
  • n: The number of top values to return.
  • ties_mode: Defines how to handle duplicate values.
  • index: The column containing the numerical values to rank.

Now, let’s explore different ways to find top N values with criteria in Google Sheets using various ties_mode options.

1. Find Top N Values with Criteria – Exact Top N Values

To find the top three sales values for January, use this formula:

=SORTN(FILTER(A2:C, A2:A="Jan"), 3, 0, 3, FALSE)

How It Works:

  • FILTER(A2:C, A2:A="Jan"): Filters the dataset for January.
  • SORTN(..., 3, 0, 3, FALSE): Sorts by Column 3 (Sales) in descending order and returns the top 3 values.

Result:

MonthItemSales
JanProduct 198
JanProduct 298
JanProduct 389

👉 This method only returns exactly three values, even if there are duplicates.

2. Find Top N Values + Duplicates of the Nth Value

This formula returns the top N values plus any additional duplicates of the Nth value.

=SORTN(FILTER(A2:C, A2:A="Jan"), 3, 1, 3, FALSE)

Result:

MonthItemSales
JanProduct 198
JanProduct 298
JanProduct 389
JanProduct 489
JanProduct 589

👉 Since 89 is the third-highest value, all rows with 89 are also included.

3. Find Top N Unique Values

To return only the top 3 unique sales values, use:

=SORTN(FILTER(A2:C, A2:A="Jan"), 3, 2, 3, FALSE)

Result:

MonthItemSales
JanProduct 198
JanProduct 389
JanProduct 686

👉 This formula ignores duplicates and returns only unique values.

4. Find Top N Unique Values + All Their Duplicates

If you need top N unique values plus all their duplicates, try:

=SORTN(FILTER(A2:C, A2:A="Jan"), 3, 3, 3, FALSE)

Result:

MonthItemSales
JanProduct 198
JanProduct 298
JanProduct 389
JanProduct 489
JanProduct 589
JanProduct 686

👉 This formula returns top unique values along with all duplicate instances.

Adding Multiple Criteria

To apply multiple criteria, modify the FILTER function accordingly.

For example, to exclude “Product 1” from the evaluation:

=FILTER(A2:C, A2:A="Jan", B2:B<>"Product 1")

If you want to exclude both “Product 1” and “Product 5”:

=FILTER(A2:C, A2:A="Jan", (B2:B<>"Product 1")*(B2:B<>"Product 5"))

Conclusion

Finding the top N values with criteria in Google Sheets is simple and flexible with FILTER and SORTN. With four different tie-handling options, you can customize the results to match your needs—whether you want exact values, unique values, or duplicates included.

By understanding these formulas, you can efficiently extract the top N values for any dataset without manual filtering or sorting!

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.