How to Rank Within Group in Excel (3 Methods)

excel rank within group
Get FREE Advanced Excel Exercises with Solutions!

While working with a large database in Microsoft Excel, sometimes we need to rank our data within the group. Our today’s dataset is about different types of Products and electronic accessories. In this article, we’ll learn three quick and suitable ways how to Excel rank within group by using the SUMPRODUCT, COUNTIFS, and SUM Functions.


Rank Within Group in Excel: 3 Suitable Ways

Let’s say, we have a dataset that contains information about different types of Products and electronic accessories. Their Category and Quantity are given in columns C and D respectively. We’ll rank these Products and electronic accessories within their Category and Quantity in column E by using the SUMPRODUCT, COUNTIFS, and SUM Functions. Here’s an overview of the dataset for today’s task.

excel rank within group


1. Apply the SUMPRODUCT Function to Rank Within Group in Excel

First of all,  we will use the SUMPRODUCT function to rank within a group in Excel. This is the easiest and time-saving way to rank within a group. Let’s follow the steps below to learn!

Step 1:

  • First, select cell E5 to rank the Products and electronic accessories.

Apply the SUMPRODUCT Function to Rank Within Group in Excel

  • Hence, type the SUMPRODUCT function in the Formula Bar. The SUMPRODUCT function in the Formula Bar is,
=SUMPRODUCT((C5=C$5:C$15)*(D5<D$5:D$15))+1
  • Where (C5=C$5:C$15) part returns TRUE/FALSE based on products category, and (D5<D$5:D$15) part checks the quantity of the products if D5 is less than any value of array D$5:D$15 then it returns TRUE otherwise And +1 is the number series that starts from 1.

Apply the SUMPRODUCT Function to Rank Within Group in Excel

  • Further, press Enter on your keyboard and you will be able to get the return of the SUMPRODUCT function and the return is 4.

Step 2:

  • After that, place your cursor on the bottom right side of cell E5 and a Fill Handle sign pops up. Now, drag the sign downward.

  • While completing the above process, you will be able to rank within the group by using the SUMPRODUCT function which has been given in the below screenshot.


2. Perform the COUNTIFS Function to Rank Within Group in Excel

You can also use the COUNTIFS function to rank within a group in Excel. To rank within a group in Excel by using the COUNTIFS function, follow the instructions below to learn!

Steps:

  • First of all, select an empty cell where we will type the COUNTIFS function, from our data we’ll select cell E5.

Perform the COUNTIFS Function to Rank Within Group in Excel

  • Now, type the following formula in the Formula Bar:
=COUNTIFS(C$5:C$15,C5,D$5:D$15, ">"&D5)+1
  • Where C$5:C$15 = criteria range1, and C5= criteria1 that means the products category.
  • D$5:D$15 = criteria range2, and “>”&D5= criteria2 which matches greater than the value of cell D5.

Perform the COUNTIFS Function to Rank Within Group in Excel

  • Hence, press Enter on your keyboard and you will be able to get the return of the COUNTIFS function and the return is 4.

  • Hence, autofill the formula by using the Fill Handle, and, finally, you will get your desired output which has been given in the below screenshot.

Perform the COUNTIFS Function to Rank Within Group in Excel

Read More: Ranking Based on Multiple Criteria in Excel


3. Use the SUM Formula to Rank Within Group in Excel

Last but not least, in this method, we will use an array formula named SUM function. Let’s follow the steps below.

Step 1:

  • To rank within-group by applying the SUM function, select cell E5 first.

Use the SUM Formula to Rank Within Group in Excel

  • Hence, type the following formula in the Formula Bar:
=SUM((C5=$C$5:$C$15)*(D5<$D$5:$D$15))+1
  • Where C5=$C$5:$C$15 matches the category of the products which is equal to the value of cell C5.
  • $D$5:$D$15 ranges the value that matches the greater than cell D5.

Use the SUM Formula to Rank Within Group in Excel

  • After that, press Enter on your keyboard and you will get 4 as the return of the SUM function.

Step 2:

  • Further, autofill the SUM function by using the Fill Handle, and, finally, you will get your desired output which has been given in the below screenshot.

Use the SUM Formula to Rank Within Group in Excel

Read More: Excel Formula to Rank with Duplicates


Things to Remember

👉 #N/A error occurs when the number is not found by the SUMPRODUCT function in the reference cell.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

I hope all of the suitable methods mentioned above to rank within-group will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo