[SOLVED] how can I rank within a filtered list in Excel?

[Brandon]

I'd like to have a dynamic report that ranks my lists based on an
autofiltered list. In other words, I'd like to toggle the criteria that
qualifies the lists content, and have excel generate "ranks" based on the
values returned. Currently the rankings are based on the overall list,
includeing the records that are filtered out.

[Domenic]

Assuming that Column B contains the values to be ranked, and the first
row contains your headers/labels...

C2, copied down:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$2:$B$10,ROW($B$2:$B$10)-MIN(ROW($B$2:$B$
10)),0,1)),--(B2<$B$2:$B$10))+1

Hope this helps!

In article <3BEB900D-669D-4938-8A1B-154D0B58CF62@microsoft.com>,
"Brandon" <Brandon@discussions.microsoft.com> wrote:

> I'd like to have a dynamic report that ranks my lists based on an
> autofiltered list. In other words, I'd like to toggle the criteria that
> qualifies the lists content, and have excel generate "ranks" based on the
> values returned. Currently the rankings are based on the overall list,
> includeing the records that are filtered out.

[Economic]

I was looking for a message on ranking a filtered list and noticed the thread. Let me just thank you for your response to this persons query as I can tell he never thanked you. It does work and it works well.


thanks heaps!

[Domenic]

You're very welcome! And thank you for your feedback!

Cheers!

I was looking for a message on ranking a filtered list and noticed the thread. Let me just thank you for your response to this persons query as I can tell he never thanked you. It does work and it works well.


thanks heaps!

[want2bgr8@xl]

Hi,
I know this post is old but it helped me out tremendously. Now I would like to like the anatomy of this formula...what makes it tick. As of right now I could not explain it to someone.

Assuming that Column B contains the values to be ranked, and the first
row contains your headers/labels...

C2, copied down:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$2:$B$10,ROW($B$2:$B$10)-MIN(ROW($B$2:$B$
10)),0,1)),--(B2<$B$2:$B$10))+1

Hope this helps!

In article <3BEB900D-669D-4938-8A1B-154D0B58CF62@microsoft.com>,
"Brandon" <Brandon@discussions.microsoft.com> wrote:

> I'd like to have a dynamic report that ranks my lists based on an
> autofiltered list. In other words, I'd like to toggle the criteria that
> qualifies the lists content, and have excel generate "ranks" based on the
> values returned. Currently the rankings are based on the overall list,
> includeing the records that are filtered out.