Please enable JavaScript to view the comments powered by Disqus.


SUMPRODUCT(...--(...C2<SUBTOTAL(...9,OFFSET(...$C$2,ROW(...$C$2:$C$6)-ROW(...$C$2),0))))+1

Rank on Filtered List 

Calculates dynamic rank for visible cells given all the numbers are positive. The SUMPRODUCT function is an array and it will calculate for each row found in ROW(C2:C6)Given the sheet is filtered and only Region A is visible.


Used Functions

Category: Math and trigonometry

Returns the sum of the products of corresponding array components

Syntax

SUMPRODUCT(array1, [array2], [array3], ...)

Category: Math and trigonometry

Returns a subtotal in a list or database

Syntax

SUBTOTAL(function_num,ref1,[ref2],...)

Category: Lookup and reference

Returns a reference offset from a given reference

Syntax

OFFSET(reference, rows, cols, [height], [width])

Category: Lookup and reference

Returns the row number of a reference

Syntax

ROW([reference])

Lets chat on this