RANKING IN EXCEL WITH MULTIPLE CRITERION

Question

I have an Excel file in which the marks of students of classes 6-10 and 5 different branches of our organization.

Each branch will have classes 6, 7, 8, 9 and 10.

I gave ranks for individual branches.

Now, I want to give ranks for each class in the entire branch.

For suppose in the branch VN students of class 6 will have ranks.

Now I want to give rank for all branches of class 6 using RANK.EQ and COUNTIFS combination.

The criteria for giving ranks is HIGHEST OF TOTAL / MAT / PHY / CHE / BIO.

Keeping the sheet as it is instead of separating each class to a different sheet I want to give final ranks for org.

i.e. branch must also be a criteria for giving ranks.

Branch Rank and Org rank are different.

SAMPLE SHEET

Answer 1

Hi ABDUL RAHMAN SYED,

Thanks for visiting Microsoft Community.

Sounds like you want to get the rank of a specific class across all branches. Please try the following steps:

1. For the "Class" column, click on the Sorting & Filtering option in the Home tab, then choose Filter. In the filter criteria, select a class—let's use Class 6 as an example.

2. Select all columns from "S.NO." to "Rank," then click on the Sort command in the Data tab. In the window that pops up, add your five sorting criteria sequentially and set them all to sort by cell value in descending order. See the illustration below for guidance.

3. At this point, you should theoretically be able to see the sorted results.

If you have any further questions or need additional assistance, please feel free to reply.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist

Answer 2

Hi Johathan! Thank you for your response.

But I want the result to be populated with a formula so that it will be dyanamic.