How to rank values within each group in Excel and Google Sheets

Excel & Google Sheets

=COUNTIFS($A$2:$A$6,A2,$B$2:$B$6,">"&B2)+1

✓

Verified example

Computed by a real spreadsheet engine on the sample data below.

=COUNTIFS($A$2:$A$6,A2,$B$2:$B$6,">"&B2)+1→2

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Formula

Runs server-side · free · no signup

1Set up columns: Group (A), Value (B), Rank Within Group (C).
2In C2, enter =COUNTIFS($A$2:$A$6,A2,$B$2:$B$6,">"&B2)+1.
3COUNTIFS counts how many rows share the same group and have a higher value; adding 1 converts that count to a rank.
4Copy the formula down for all rows — each row gets ranked among its group peers.

To rank ascending (lowest = 1), change > to < in the COUNTIFS criterion: =COUNTIFS($A$2:$A$6,A2,$B$2:$B$6,"<"&B2)+1.

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.

Tied values receive the same rank (like RANK with the default tie behaviour), and the next rank is skipped.

Yes. Column A can be any group label (text or number) and column B should be the numeric values to rank.

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: May 28, 2026