How to Rank by Multiple Criteria in Excel

To rank data by multiple criteria in Excel, you can use a combination of the RANK and COUNTIFS functions. Here's a step-by-step guide on how to do it:

1. Prepare your data: Make sure your data is organized in columns with headers. For this example, we'll use a dataset containing Student Names, Test Scores, and Ages.

Example:

2. Add a new column for the rank: In this example, we'll add a column called "Overall Rank" to the right of the "Age" column.
3. Use the RANK and COUNTIFS functions: In the first row of the "Overall Rank" column, use the following formula to rank the students by Test Score and Age (assuming the first row contains headers):

=RANK(B2, $B$2:$B$6) + COUNTIFS($B$2:$B$6, B2, $C$2:$C$6, ">" & C2) - 1

4. Copy the formula down: Drag the formula down to fill the entire "Overall Rank" column, so it applies to each row.
5. Sort the data by rank: Click on any cell within the dataset, then go to the "Data" tab and click "Sort" in the "Sort & Filter" group. Choose "Overall Rank" as the column to sort by and make sure it's sorted from smallest to largest.

Here's the final dataset with the Overall Rank:

In this example, the students are ranked by their Test Score (higher is better) and Age (younger is better). The formula calculates the rank based on Test Scores, then adds additional rank points for each student with the same Test Score but a younger age.