How to Rank Within a Group in Excel (3 Methods)

Dataset Overview

Suppose we have a dataset containing information about various types of products and electronic accessories. The data includes their Category (in column C) and Quantity (in column D). Our goal is to rank these items within their respective Category and Quantity, using a new column (E).

Method 1 – Using the SUMPRODUCT Function

• Start by selecting cell E5, where we want to rank the products and electronic accessories.

• In the Formula Bar, enter the following SUMPRODUCT function:

Here,

• (C5=C$5:C$15) checks if the product category matches.
• (D5<D$5:D$15) verifies if the quantity (D5) is less than any value in the range D$5:D$15.
• The +1 ensures the ranking starts from 1.

• Press Enter to get the result (e.g., 4).

• Drag the Fill Handle downward to rank other items within the group.

Method 2 – Using the COUNTIFS Function

• Select an empty cell (e.g., E5) for the COUNTIFS function.

• Enter the following formula in the Formula Bar:

Here,

• C$5:C$15 is the criteria range for product category.
• C5 is the specific product category.
• D$5:D$15 is the criteria range for quantity.
• “>”&D5 checks for quantities greater than the value in cell D5.

• Press Enter to get the result (e.g., 4).

• Autofill the formula to rank other items.

Read More: Ranking Based on Multiple Criteria in Excel

Method 3 – Using the SUM Function (Array Formula)

• Select cell E5.

• Enter the following formula in the Formula Bar:

Here,

• (C5=$C$5:$C$15) matches the product category.
• (D5<$D$5:$D$15) compares the quantity with values in the range D$5:D$15.

• Press Enter to get the result (e.g., 4).

• Autofill the SUM function to rank other items.

Read More: Excel Formula to Rank with Duplicates

Things to Remember

If the SUMPRODUCT function doesn’t find a number in the reference cell, it returns an #N/A error. Double-check your data to avoid this issue.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• How to Rank with Ties in Excel
• How to Calculate the Top 10 Percent in Excel
• Ranking Data in Excel with Sorting
• How to Rank in Excel Highest to Lowest
• Rank IF Formula in Excel

<< Go Back to Excel RANK Function | Excel Functions | Learn Excel