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

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.

### 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 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.

• 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.

• 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.

### 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 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.

• 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.

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.

## 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

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF