Rank Function Only on Visible Cells

[cdubs34]

Basically I want to use the rank function (i.e. 1,2,3,4) for only visible cells. So I want to avoid getting a rank of 1 2 4 where the third value is a hidden cell. Thank you in advance for your help.

 

[Marcelo Branco]

Could you post a data sample and expected results?

M.

 

[Domenic]

To rank from highest to lowest visible cells that are the result of either an AutoFilter or manually hidden rows, assuming that A2:A100 contains the data, try...

B2, confirmed with CONTROL+SHIFT+ENTER, and copy down:

=SUM(IF(SUBTOTAL(103,OFFSET($A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2),0,1))>0,IF(A2<$A$2:$A$100,1)))+1

Adjust the ranges accordingly. To rank from lowest to highest, replace...

A2<$A$2:$A$100

with

A2>$A$2:$A$100

 

[cdubs34]

Thank you for the response Domenic. I have mirrored your formula above for my data set, however when I drag the formula down from the first cell I just get 1 for each "ranking". Please let me know if you know why this is. Thanks.

 

[cdubs34]

Could I send my excel spreadsheet to someone as it would be easier to visualize the issue that way? Thanks

 

[barry houdini]

....when I drag the formula down from the first cell I just get 1 for each "ranking".

That sounds like you haven't used CTRL+SHIFT+ENTER

Put the formula in the first cell, press F2 to select the formula and then hold down CTRL and SHIFT keys and press ENTER. Curly braces like { and } should appear around the formula in the formula bar. Now copy down

An alternative would be to use SUMPRODUCT to get a "regular" formula, i.e.

=SUMPRODUCT(SUBTOTAL(103,OFFSET($A$2:$A$100,ROW($A$2:$A$100)-ROW($A$2),0,1)),(A2<$A$2:$A$100)+0)+1

 

[cdubs34]

AHHH brilliant. Thank you Houdini, I forgot the {}. Thanks also again to Domenic