I have a list of product categories and within each category is multiple
product models and their inventory value for the current month. I want to
rank each product model within it's category based on it's inventory value.
How do I create a formula that does this without having to sort the list each
time and determine the range to base the rank on. I just want one formula
that determines which category the product model is in and rank it based on
the inventory value

Try this:

A2:A20 = categories
C2:C20 = inventory value

Enter this formula in D2 and copy down as needed:

=SUMPRODUCT(--(A$2:A$20=A2),--(C2<C$2:C$20))+1

--
Biff
Microsoft Excel MVP


"vnvkatz" wrote in message
...
I have a list of product categories and within each category is multiple
product models and their inventory value for the current month. I want to
rank each product model within it's category based on it's inventory
value.
How do I create a formula that does this without having to sort the list
each
time and determine the range to base the rank on. I just want one formula
that determines which category the product model is in and rank it based
on
the inventory value

Works perfect. Thanks

"T. Valko" wrote:

Try this:

A2:A20 = categories
C2:C20 = inventory value

Enter this formula in D2 and copy down as needed:

=SUMPRODUCT(--(A$2:A$20=A2),--(C2<C$2:C$20))+1

--
Biff
Microsoft Excel MVP


"vnvkatz" wrote in message
...
I have a list of product categories and within each category is multiple
product models and their inventory value for the current month. I want to
rank each product model within it's category based on it's inventory
value.
How do I create a formula that does this without having to sort the list
each
time and determine the range to base the rank on. I just want one formula
that determines which category the product model is in and rank it based
on
the inventory value

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"vnvkatz" wrote in message
...
Works perfect. Thanks

"T. Valko" wrote:

Try this:

A2:A20 = categories
C2:C20 = inventory value

Enter this formula in D2 and copy down as needed:

=SUMPRODUCT(--(A$2:A$20=A2),--(C2<C$2:C$20))+1

--
Biff
Microsoft Excel MVP


"vnvkatz" wrote in message
...
I have a list of product categories and within each category is multiple
product models and their inventory value for the current month. I want
to
rank each product model within it's category based on it's inventory
value.
How do I create a formula that does this without having to sort the
list
each
time and determine the range to base the rank on. I just want one
formula
that determines which category the product model is in and rank it
based
on
the inventory value