Ranking within a Filtered Worksheet

[Ralph Noble]

Anyone know if you can rank filtered data in Excel? I want to filter
out some rows and rank the rest but the hidden data seems to be
screwing up the ranking. Essentially, the worksheet has three sets of
data that I would like to rank separately.

Thanks,

Ralph Noble

 

[Debra Dalgleish]

You can add columns to your table, to calculate the rank.

For example, to rank an item in column G, with data starting in row 2:
--In cell H1, add the heading RankAmt1
--In cell H2, enter the following formula: =SUBTOTAL(9,G2)
--In cell I1, add the heading Rank1
--In cell I2, enter the following formula: =RANK(H2,$H$2:$H$400)
where 400 is the last row of data
--Copy the formulas down to the last row of data

Create similar columns for other columns to be ranked