Search code examples
excelranksubtotal

Rank like Subtotals

It's possible use Rank like Subtotals, that only use de showing data?.

If I filter data by a column, I want than Rank function only use these datas

Example

A   B   C   The column C its Rank of column B
a   5   3       
b   9   1  
a   2   4
c   7   2

Now if I apply a filter in column A for value 'a'

A   B   C    I want the rank recalculate with this new data
a   5   1    --> column C change from value 3 to value 1
a   2   2    --> column C change from value 4 to value 2

Thanks

Solution

  • You cannot do it by using RANK() formula, but you can create customizable rank formula. For this, You should add column at the and of table to indicate the row visibility, and put this formula into this column:

    =(AGGREGATE(3;5;B2)>0)+0

    Then put this formula into column C:

    =SUMPRODUCT(($B$2:$B$9<B2)*($E$2:$E$9=1))+1

    To check real example, download my sample file