How do I rank people across several sheets?

Stack the sheets into one table first, then use RANK.EQ (or RANK() OVER in SQL) on the combined column.

Should I rank on revenue or % of target?

Percentage of target is fairer when targets differ by territory; show both.

How do I rank within each region?

Use COUNTIFS on region and higher revenue, plus 1, or RANK() OVER (PARTITION BY region ...) in SQL.

What's the difference between RANK and DENSE_RANK?

RANK skips numbers after a tie (1, 2, 2, 4); DENSE_RANK doesn't (1, 2, 2, 3).

How do I check no file was missed?

Compare the sum of each regional file with the combined total after stacking.

Can I rank on several measures at once?

Rank each measure separately and show the ranks side by side, rather than inventing a combined score.

How do sales reps rank across all regions?

By the Analistable team · Updated 2026-10-07 · 3 min read

Stack the regional files with a Region column, then rank on revenue and on % of target — the fairer measure when targets differ. In the example, Cai (South) leads on revenue and on attainment (113%), while Ana (North) ranks third on revenue but second on attainment (107%).

Part of our guide: How to answer questions across multiple spreadsheets

The data

Three regional files, stacked
Region	Rep	Revenue	Target
North	Ana	48200	45000
North	Ben	39100	45000
South	Cai	61950	55000
South	Dee	52300	55000
West	Eli	41800	42000

In a spreadsheet

% of target     =[@Revenue] / [@Target]
Rank (revenue)  =RANK.EQ([@Revenue], [Revenue])
Rank in region  =COUNTIFS([Region], [@Region], [Revenue], ">" & [@Revenue]) + 1

In SQL

SELECT RANK() OVER (ORDER BY revenue DESC) AS rank,
       rep, region, revenue,
       ROUND(100.0 * revenue / target, 1) AS pct_of_target
FROM reps;

Result
rank	rep	region	revenue	pct_of_target
1	Cai	South	61950	112.6
2	Dee	South	52300	95.1
3	Ana	North	48200	107.1
4	Eli	West	41800	99.5
5	Ben	North	39100	86.9

Ordered by attainment instead: Cai 112.6%, Ana 107.1%, Eli 99.5%, Dee 95.1%, Ben 86.9%. Dee is second on revenue but fourth on target.

Before publishing a league table

Make sure every file covers the same period and uses the same revenue definition (booked vs invoiced).
Check rep names are spelled the same in every file, or a rep who moved region appears twice.
Add a rank within region with RANK() OVER (PARTITION BY region ORDER BY revenue DESC) in SQL.

Ask it in Analistable: “Rank reps across all regional files by percentage of target.” Stacking the files: combining sheets into one.

In Google Sheets

=SORT({Reps!B2:B6, Reps!A2:A6, Reps!C2:C6, ARRAYFORMULA(Reps!C2:C6 / Reps!D2:D6)}, 4, FALSE)

This sorts reps by percentage of target in one formula. Format the fourth column as a percentage.

Common problems

Reps who moved region mid-year appear in two files — sum their revenue across files before ranking.
Different currencies in international regions: convert first.

Rank within each region

SELECT region, rep,
       RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank_in_region
FROM reps
ORDER BY region, rank_in_region;

Result
region	rep	rank_in_region
North	Ana	1
North	Ben	2
South	Cai	1
South	Dee	2
West	Eli	1

Region and team totals

Attainment by region
Region	Revenue	Target	% of target
North	87300	90000	97.0%
South	114250	110000	103.9%
West	41800	42000	99.5%
All	243350	242000	100.6%

The team as a whole is just over target. North is below despite Ana's 107%, because Ben is at 86.9%. Totals like these are a useful check that no file was dropped when stacking: the sum of the regional files must equal the combined total.

Ties and rank functions

RANK.EQ gives tied reps the same rank and skips the next number (1, 2, 2, 4). RANK.AVG gives both 2.5.
In SQL, DENSE_RANK() gives 1, 2, 2, 3 without gaps; ROW_NUMBER() breaks ties arbitrarily, so avoid it for league tables.
Rank attainment with RANK.EQ([@[% of target]], [% of target]) — the same function on the percentage column.

Troubleshooting

Common problems
Symptom	Cause	Fix
Rep ranked twice	Name spelled differently in two files	Use a rep ID or a name mapping
Ranks don't start at 1	Hidden or filtered rows still in the range	Rank the stacked table, not a filtered view
% of target over 1,000%	Target is monthly, revenue is annual	Align periods

Repeat it each month or quarter

Ask every region for the same columns in the same order (Region, Rep, Revenue, Target) so stacking needs no rework.
Keep the period in a column when you stack several months, and rank on year-to-date revenue against year-to-date target rather than one month, which is noisy for reps with large deals.
Save each published ranking; comparing a rep's rank with last quarter's shows momentum that a single table hides.

Frequently asked questions

How do I rank people across several sheets?: Stack the sheets into one table first, then use RANK.EQ (or RANK() OVER in SQL) on the combined column.
Should I rank on revenue or % of target?: Percentage of target is fairer when targets differ by territory; show both.
How do I rank within each region?: Use COUNTIFS on region and higher revenue, plus 1, or RANK() OVER (PARTITION BY region ...) in SQL.
What's the difference between RANK and DENSE_RANK?: RANK skips numbers after a tie (1, 2, 2, 4); DENSE_RANK doesn't (1, 2, 2, 3).
How do I check no file was missed?: Compare the sum of each regional file with the combined total after stacking.
Can I rank on several measures at once?: Rank each measure separately and show the ranks side by side, rather than inventing a combined score.