Dynamic Rank when filtering

[BryantK]

Hi! I'm trying to put together a way to dynamically rank a file in data set in excel when using filters.

For example in the attached file, that the formula ranks everything normally when there's no filters applied, but when I select "Orange" under the "Color" column, then it dynamically ranks only the two lines under Orange, then when I remove the filter, it goes back to the original ranking.

Thanks!

 

[Jeffrey Mahoney]

Please Try this. When you filter, the Subtotal formula will multiply zero with your Sales figure and cause the hidden rows to be zero

Book1
	A	B	C	D	E
1
2	Name	Color	Sales	Rank	Vis Sales
3	S	Orange	10000000	1	10000000
4	A	Red	100000	2	100000
5	B	Red	40000	3	40000
6	C	Blue	30000	4	30000
7	D	Blue	20000	5	20000
8	F	Green	15000	6	15000
9	D	Green	10000	7	10000
10	G	Orange	5000	8	5000
Sheet1

Cell Formulas
Range	Formula
D3:D10	D3	=RANK(E3,$E$3:$E$10)
E3:E10	E3	=SUBTOTAL(103,A3)*C3