Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to rank values with a tiebreaker in Excel and Google Sheets
Topic:RANK & statistical aggregatesExcel & Google Sheets
=RANK(B2,$B$2:$B$6,0)+COUNTIFS($B$2:$B$6,B2,$A$2:$A$6,"<"&A2)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Put the primary score to rank in B2:B6 and the tiebreaker value in A2:A6 (e.g. an entry date — the earlier date wins the tiebreak).
- 2In C2 enter =RANK(B2,$B$2:$B$6,0)+COUNTIFS($B$2:$B$6,B2,$A$2:$A$6,"<"&A2). RANK gives the base position; COUNTIFS counts rows with the same score but a smaller tiebreaker value, pushing later entries down.
- 3Copy C2 down through C6. Each row should receive a unique rank from 1 to 5.
- 4Verify by sorting column C: ranks should form a continuous 1-through-n sequence with no duplicates or gaps.
- 5For descending rank (smallest score = rank 1), change the RANK third argument from 0 to 1 and the COUNTIFS comparison from "<" to ">".
Tips
- This pattern works for any secondary criterion — date, name (alphabetical), numeric ID — as long as the comparison operator is correct for the tiebreaker direction.
- To add a third tiebreaker, chain another COUNTIFS term: +COUNTIFS($B$2:$B$6,B2,$A$2:$A$6,A2,$D$2:$D$6,"<"&D2). The pattern stacks cleanly.
- RANK.EQ is identical to RANK in Excel 2010+. RANK.AVG assigns the average position to ties and is useful for statistical work but will not produce unique ranks.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Why does my formula still produce gaps like 1, 1, 3 instead of 1, 2, 3?
Without the COUNTIFS addition you get standard RANK behavior. Confirm the COUNTIFS criteria ranges match the data ranges exactly and the comparison operator is correct for your tiebreaker direction.
Can I use SORTBY and SEQUENCE to rank instead?
In Excel 365 and Sheets, SORTBY then assigning 1-n with SEQUENCE is clean but produces a separate sorted list rather than in-place ranks next to the original data.
More on RANK & statistical aggregates
See all →Reference
RANK.EQ
Returns the rank of a number in a list, assigning the same rank to ties (same as RANK). Essential for leaderboards.
How-toHow to rank values
Rank a number against a list with RANK — highest first or lowest first. Works the same in Excel and Google Sheets.
ReferenceRANK.AVG
Returns the rank of a number, averaging ranks for ties. Useful in statistical analysis where tied ranks should not cluster.
How-toHow to calculate standard deviation
Measure how spread out your numbers are with STDEV for a sample or STDEVP for a whole population. Works in Excel and Google Sheets.
ReferencePERCENTILE
Returns the k-th percentile value from a dataset. Useful for performance benchmarking and threshold analysis.
How-toHow to calculate a weighted average
Combine values and their weights with SUMPRODUCT divided by the total weight. Works the same in Excel and Google Sheets.