Rank in Excel by Multiple Criteria: Formulas and Tips

Formulas & functions · Updated November 18, 2026

With primary scores in B2:B5, secondary scores in C2:C5, and a third tie-breaker in D2:D5, enter this in E2 and fill down to rank all three criteria in order: =IF(B2="","",1+COUNTIF($B$2:$B$5,">"&B2)+COUNTIFS($B$2:$B$5,B2,$C$2:$C$5,">"&C2)+COUNTIFS($B$2:$B$5,B2,$C$2:$C$5,C2,$D$2:$D$5,">"&D2)). It ranks higher primary scores first, then higher secondary scores, then higher third scores; fully identical records receive the same rank.

What ranking by multiple criteria means

Ranking by multiple criteria means comparing records in a defined priority order. The first criterion decides the order whenever values differ. The next criterion is consulted only when the first is tied, and later criteria break any remaining ties. This is often called a hierarchical or lexicographic ranking.

For example, a competition might rank students by total points, then by a practical-exam score if total points are equal, then by attendance if both scores are equal. That is different from combining all three measures into one weighted score. In a hierarchical ranking, a small difference in the primary score always takes precedence over any secondary score.

Be clear about the rule before building a formula. “Rank by score and attendance” could mean score first with attendance as a tie-breaker, or a weighted combination of score and attendance. Those methods can produce different results. The formula above implements the first interpretation.

How the formula works

The formula counts how many records should come ahead of the current row, then adds 1 to convert that count into a rank. The first COUNTIF counts records with a higher value in the primary column. The first COUNTIFS counts records tied on the primary criterion but higher on the secondary one. The final COUNTIFS counts records tied on the first two criteria but higher on the third.

The dollar signs keep the comparison ranges fixed as you fill the formula down, while B2, C2, and D2 change to refer to each row. The IF check leaves the result blank when the primary cell is empty, rather than displaying a rank for an unused row. The example assumes the four records occupy rows 2 through 5; adjust every range to match your data.

This counting method is useful because the tie-breakers are applied only when earlier criteria match. A record with a lower primary score cannot outrank one with a higher primary score, even if it has excellent secondary and third scores.

Worked example with three criteria

Suppose a contest uses points as the primary criterion, a skills score as the secondary criterion, and a final-round score as the third. Higher values win in all three columns:

Name Points Skills Final round Rank
Ava 92 8 4 3
Ben 92 9 2 1
Chen 88 10 5 4
Dia 92 9 1 2

Ben and Dia tie with 92 points and a skills score of 9. The final-round score breaks that tie: Ben’s 2 is greater than Dia’s 1, so Ben ranks ahead. Ava also has 92 points, but her skills score of 8 is lower, so she follows both of them. Chen’s higher skills and final-round scores do not overcome a lower primary score of 88. The resulting order is Ben, Dia, Ava, Chen.

To check the arithmetic for Ava, one record has a higher primary score? No: nobody has more than 92. Among records tied at 92, Ben and Dia each have a higher skills score than Ava, so two records are counted ahead of her. Her rank is therefore 1 + 2 = 3. This check illustrates why later criteria are considered only within ties on the criteria before them.

Changing the direction of a criterion

The sample ranks every criterion from high to low. Some real rankings mix directions. For example, a race might prioritize the greatest points total, then the shortest completion time. In that case, the primary comparison still counts values greater than the current value, but the time tie-breaker must count values less than the current time.

In the formula, each criterion’s direction is controlled by its comparison operator: greater-than means higher values come first, while less-than means lower values come first. Change the operator for only the criteria that should run in the opposite direction. This also works for measures such as error counts, where fewer errors may be preferable.

Confirm that the data is stored as actual numbers, dates, or times, not text that merely looks numeric. Text values can sort differently from numeric values, and a text time may not compare as an elapsed time. Apply a consistent rule to every row before relying on the ranks.

How ties affect the rank numbers

When records match on every criterion in the formula, they receive the same rank. The next rank leaves a gap, which is known as competition ranking. For instance, if two records tie for first, both are ranked 1 and the next distinct record is ranked 3. That behavior follows naturally from counting how many records are ahead.

If you need every row to have a unique position, add a final tie-breaker that is unique, such as a registration number or a name when names are guaranteed to differ. Decide which direction that final field should use, then add another COUNTIFS term that checks equality on all earlier fields and counts values ahead in the unique field. A unique tie-breaker makes the ordering reproducible, but it also means the final field decides who comes first when all substantive measures are equal.

Do not add an arbitrary tie-breaker without documenting it. If the intended result is a shared rank, identical records should stay tied. If the intended result is a complete ordered list, use a legitimate rule such as entry time or ID number.

Ranking versus sorting in Excel

A rank formula calculates a position in a separate column; it does not rearrange the source rows. If you only need to display records in priority order, Excel’s multi-level sort may be simpler. Select the data, choose Data > Sort, choose the primary column and order, select Add Level, then specify the secondary and any later columns. Include the whole record range so names and associated values stay together.

Sorting and ranking answer different questions. A sort changes the visible row order, while a rank column records each row’s position without moving it. A formula is useful when the rank must appear beside each record, be used in another calculation, or update as values change. Sorting is useful for reviewing a list in order or preparing a report.

For a simple ranking based on one numeric column, Excel has the RANK.EQ function. With scores in B2:B5 and the score to rank in B2, =RANK.EQ(B2,$B$2:$B$5,0) ranks the largest score as 1; the final argument 0 specifies descending order. RANK.EQ ranks one numeric measure, so it does not by itself apply a second criterion as a tie-breaker. The COUNTIF/COUNTIFS approach supplies that extra logic.

Common problems and checks

  • Ranges do not match: Each COUNTIFS criteria range must have the same dimensions as the other criteria ranges. A range that starts or ends on a different row can cause an error or an incorrect comparison.
  • Formula copied incorrectly: Keep dollar signs around the data ranges so they stay fixed when filling down. Check that the row references for the current record do change.
  • Blank or incomplete records: The sample formula checks only whether the primary cell is blank. If a record is valid only when several cells are filled, define that rule explicitly and adapt the blank check; otherwise incomplete rows may receive ranks.
  • Unexpected ties: Check whether the compared values really differ. A displayed value may be rounded while the underlying decimals are different. Increase the displayed decimal places or use a deliberate rounding rule if the displayed precision is what should count.
  • Wrong direction: Verify each greater-than or less-than comparison against the real rule. “Lowest time wins” requires the opposite direction from “highest score wins.”
  • Text and numbers mixed together: Convert entries to a consistent data type before ranking. Imported values can look numeric while being stored as text.

For longer lists, replace the example’s ending row with the actual last row, or use an Excel Table so new records are easier to include. When using a Table, verify that the formula’s comparison ranges expand as intended. Test a few known cases—one clear winner, a primary tie, and a tie across all criteria—before using the results for decisions.

When a weighted score is a better fit

Sometimes no criterion should automatically dominate the others. A hiring rubric, product comparison, or project-priority model may assign explicit weights to several measures instead. In that situation, calculate a combined score using the agreed weights, then rank that single score. For example, a model could give one measure 60% weight and another 40%, provided the measures are on compatible scales or have been normalized appropriately.

Weighted scoring is not inherently more objective: the chosen weights and treatment of different scales determine the outcome. A raw score measured from 0 to 100 can overwhelm another measured from 0 to 5 unless the model accounts for the different ranges. Use hierarchical ranking when the priority order is strict; use a weighted score when trade-offs are intended and the weighting rules are defensible.

Frequently Asked Questions

Can Excel rank by two criteria?

Yes. Count records with a higher primary value, then count records tied on that value but higher on the secondary value. The formula in this guide extends the same pattern to a third criterion, and more tie-breakers can be added in order.

Does RANK.EQ support multiple criteria?

RANK.EQ ranks one numeric value against one reference range. It does not accept a separate tie-breaker criterion. Use COUNTIF and COUNTIFS to build a hierarchical ranking, or calculate a justified combined score first and rank that score.

How do I rank with the lowest value first?

Use less-than comparisons for the criteria where smaller values should come first. For a single numeric measure, RANK.EQ can also rank in ascending order by setting its order argument to 1. Make sure each criterion’s direction matches the rule you intend.

Why do two rows have the same rank?

They match on every criterion included in the formula, so neither is counted ahead of the other. Add a meaningful final tie-breaker if every row needs a distinct position; otherwise, a shared rank is the correct result.

Will the formula update when I change a score?

Yes. The formula recalculates when values in its referenced ranges change. If you add records beyond the ranges named in the formula, expand those ranges or use a Table-based setup so new rows are included.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.