Rank based on category
 

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

Rank based on category
 

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

Rank based on category
 

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

Rank based on category
 

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