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.


Download Practice Workbook

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


3 Suitable Ways to Rank Within Group in Excel

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

=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 an autoFill sign pops us. Now, drag the autoFill 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.

Read More: How to Calculate Weighted Ranking in Excel (4 Ways)


Similar Readings


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

=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, to autoFill the COUNTIFS function by using the autoFill handle, and, finally, you will get your desired output that 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 (4 Cases)


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:

Use the SUM Formula to Rank Within Group in Excel

  • Hence, type the SUM function in the Formula Bar. The SUM function is,
=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, to autoFill the SUM function by using the autoFill handle, and, finally, you will get your desired output that 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 (3 Examples)


Things to Remember

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


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

Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo