Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to rank by multiple criteria in Excel and Google Sheets
Topic:RANK & statistical aggregatesHeads up: Excel and Google Sheets do this differently.
Excel
=RANK.EQ(A2, IF((B$2:B$7=B2)*(C$2:C$7=C2), A$2:A$7), 0)Google Sheets
=ARRAYFORMULA(RANK.EQ(A2:A7, IF((B2:B7=B2)*(C2:C7=C2), A2:A7), 0))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
- 1Enter your data into columns, ensuring each criterion is in its own column.
- 2In the ranking column, use the RANK.EQ function combined with an IF statement to apply multiple criteria.
- 3For Google Sheets, wrap the formula in ARRAYFORMULA to apply it across the range.
Tips
- Ensure your criteria columns do not contain any blank cells to avoid unexpected results.
- Adjust the order parameter in RANK.EQ to 1 for ascending or 0 for descending order.
- Be cautious with cell references to ensure they correctly reference the entire range of data.
Need the Google Sheets version instead? Open Sheets variant in workspace →
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Can I use this method for more than two criteria?
Yes, you can add more conditions within the IF statement using additional logical operators like AND or OR.
What happens if there are ties in the data?
RANK.EQ assigns the same rank to tied values and skips the next rank(s).
Is this method case-sensitive?
No, text comparisons in the IF statement are not case-sensitive unless specified otherwise.
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.