Md. Abdur Rahim Rasel
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.
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.
- 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.
- 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
- How to Rank Average in Excel (4 Common Scenarios)
- Rank with Ties in Excel (5 Simple Ways)
- How to Calculate Rank Percentile in Excel (7 Suitable Examples)
- Calculate the Top 10 Percent in Excel (4 Ways)
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.
- Now, type the COUNTIFS function in the Formula Bar. The COUNTIFS function is,
=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.
- 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.
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:
- To rank within-group by applying the SUM function, select cell E5 first.
- 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.
- 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.
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.
