Filter Top N per Category in Excel (Using FILTER & COUNTIFS)

You can use a combination of FILTER and COUNTIFS to filter the top N records per category in Excel. This is a powerful formula for analyzing the performance of items categorized under different groups.

Assume you are running a chain of apparel outlets. You want to find the top three best-selling T-shirts in your stores across the country. This data can help in several ways—for example, by stocking more of the best-selling brands or focusing on marketing other items through discounts, promotional sales, etc.

Generic Formula to Filter Top N per Category in Excel

=SORT(FILTER(data_range, (category_range<>"")*(COUNTIFS(category_range, category_range, rank_range, ">"&rank_range)+1<=n)), {category_range_index, rank_range_index}, {1, -1})

Formula Components:

  • data_range – The range containing the dataset.
  • category_range – The range reference for the category column.
  • rank_range – The range reference for the ranking column.
  • n – Determines the number of top records to return.
  • category_range_index – The column position of the category column within data_range.
  • rank_range_index – The column position of the ranking column within data_range.

Note: All ranges should be of equal size.

This formula works only in Excel 365 and later versions that support dynamic arrays and functions like FILTER, SORT, and COUNTIFS.

Sample Data for Filtering Top N per Category in Excel

Sample dataset for filtering top N per category in Excel, showing stores, T-shirt brands, and sales quantities

In this example, we will filter the top 2 best-selling T-shirt brands from each store.

Formula for Filtering Top 2 T-Shirts per Store

Using the above dataset, enter the following formula in cell E2:

=SORT(FILTER(A2:C13, (A2:A13<>"")*(COUNTIFS(A2:A13, A2:A13, C2:C13, ">"&C2:C13)+1<=2)), {1, 3}, {1, -1})

This formula helps filter the top N items per category in Excel.

Filtered results showing the top N T-shirt brands per store in Excel using the FILTER and COUNTIFS functions

Formula Explanation

1. Group-wise Ranking in Excel

The key part of the formula is computing the rank per group:

COUNTIFS(A2:A13, A2:A13, C2:C13, ">"&C2:C13) + 1

This assigns a group-wise rank to each item within its category.

2. Filtering the Top N per Category

FILTER(A2:C13, (A2:A13<>"")*(COUNTIFS(A2:A13, A2:A13, C2:C13, ">"&C2:C13)+1<=2))

This filters rows where:

  • The category column is not blank.
  • The rank is ≤ 2, ensuring we get the top 2 items per store.

3. Sorting the Results

SORT(..., {1, 3}, {1, -1})
  • The SORT function arranges results in ascending order by Store (column 1).
  • Within each store, the results are sorted in descending order by Sales Quantity (column 3).

How Does the Formula Handle Duplicates?

Since the formula uses ranking, it returns the top N items plus all occurrences of the Nth-ranked item. This ensures fairness and accuracy in reporting.

For example, in Store C, both Brand Y and Brand Z have 110 sales, so they are both included in the top 2.

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. With years of experience working with Google Sheets and Excel for multinational firms in Mumbai and Dubai, he has been blogging since 2012, offering practical, real-world spreadsheet solutions that professionals rely on. 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

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

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

Sort Each Row Individually in Excel Using a LAMBDA Formula

Sorting rows in Excel typically refers to rearranging entire datasets based on values in...

Sort by Field Labels Using the SORT and XMATCH Combo in Excel

Want to sort your Excel data by column names instead of column positions? Learn...

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.