How to rank, with only visible and any sort order

Hi,

as per attached example, I am trying to create a ABC ranking for the 80/20 pareto of our product sales.

How can I make it so that the ranking will only apply to visible products, so that when some are filtered, they are not calculated.

Also, how can we make it, so that the sales column can be sorted in any order (and not just cumulative). Basically, I want to be able to filter and sort - and the ranking will always show the ABC correctly, regardless.

Lastly, how to do this with only formulas!

Thanks for any support, as I've wasted hours trying to solve this without luck!

=SUBTOTAL(3,$A2:A2) in column outside of the table

oh wow, what magical genius is this!?

I need to test more, but looks to be perfect! THANKS!!

So, it's working very well, and I'm very grateful - thanks!

Just one question - the ranking only works when the sales are sorted from large to small. If the product sort is random (eg. alphabetical) - then the ABC ranking breaks.

If there a way to fix that - or is that something that cannot be avoided without more heavy duty analysis? (I'm trying to avoid pivots here for example).

Thanks!