Excel RANK by Group: A Recipe for Overall and Group Rankings with COUNTIFS
If your report needs both overall rankings and group (partial) rankings, the most reliable approach is to calculate overall rankings with RANK.EQ / RANK.AVG and create rankings within each group by counting comparisons with COUNTIFS. See the official Microsoft definitions as well.
Quick Fix — One-Line Group Ranking
=COUNTIFS($B:$B,B2, $D:$D, ">" & D2) + 1RANK.EQ vs. RANK.AVG
- RANK.EQ: Ties receive the same rank, and the next rank is skipped.
- RANK.AVG: Ties receive the average rank.
order: 0 or omitted = descending order; 1 = ascending order.
Overall Ranking
=RANK.EQ(D2, $D:$D, 0)
=RANK.EQ(D2, $D:$D, 1)Ranking by Group (Example: DIV)
=COUNTIFS($B:$B,B2, $D:$D, ">"&D2) + 1
=COUNTIFS($B:$B,B2, $D:$D, "<"&D2) + 1Tie Breaker (Score > Date > Name)
=LET(grp,$B:$B, sc,$D:$D, dt,$C:$C, nm,$A:$A,
COUNTIFS(grp,B2, sc, ">"&D2)
+COUNTIFS(grp,B2, sc, D2, dt, ">"&C2)
+COUNTIFS(grp,B2, sc, D2, dt, C2, nm, "<"&A2) + 1)Combine with Dynamic Arrays
=LET(u,UNIQUE(B2:B1000),
MAP(u,LAMBDA(d, TAKE(SORTBY(FILTER(A2:D1000, B2:B1000=d), D2:D1000, -1), 3))))Troubleshooting
| Issue | Cause | Solution |
|---|---|---|
| Rank is 0 or negative | Operator or text concatenation error | Check the “>”&cell format. |
| Unexpected tie handling | Confusing RANK.EQ and RANK.AVG | Use RANK.AVG for average ranks. |
| Group rankings are mixed up | COUNTIFS ranges have different lengths | Make all criteria ranges the same length. |
| Date comparison error | Dates stored as text | Convert with DATE or to numbers before comparing. |
| Slow performance | Excessive full-column references | Use a Table and structured references. |
Related Posts
- SUMIFS Multiple-Criteria Best Practices
- XLOOKUP: Basics and Switching from Older Lookups
- Data Cleaning: TRIM/CLEAN
- Table Basics and Benefits
- Complete Guide to VLOOKUP with Multiple Criteria
References: Microsoft Support — RANK.EQ, RANK, RANK.AVG, COUNTIFS