Ranking categories based on total (summed up) values

[kderoeck]

Hi,

I'm looking for a formula (that can be used in an excel Table) that allows me to add a ranking on row level that is calculated on the total sales per category. This means that rows from the same category should all have the same ranking. An example of the Table and the desired result:

Category Month Sales Rank
AAA OCT2016 2000 3
AAA NOV2016 6000 3
AAA DEC2016 1000 3
BBB OCT2016 3000 1
BBB NOV2016 7000 1
BBB DEC2016 5000 1
CCC OCT2016 8000 2
CCC NOV2016 4000 2
CCC DEC2016 1000 2

Category AAA has total sales of 9000 (and ranks as 3rd),
Category BBB has total sales of 15000 (and ranks as 1st),
category CCC has total sales of 1300 (and ranks as 2nd).

To make things even more challenging, I'd like to be able to get to this result without the use of intermediate step/columns

 

[Marcelo Branco]

Maybe...

Formula in D2 copied down
=SUMPRODUCT(--(SUMIF($A$2:$A$10,$A$2:$A$10,$C$2:$C$10)>SUMIF($A$2:$A$10,A2,$C$2:$C$10)),--(MATCH($A$2:$A$10,$A$2:$A$10,0)=ROW($A$2:$A$10)-ROW($A$2)+1))+1

Hope this hels

M.

 

[kderoeck]

Hi Marcelo,

This is just perfect! The formula you're suggesting does exactly what I wanted.
Many thanks for the prompt reply

In 'Table' style the formula looks like this:
=SUMPRODUCT(--(SUMIF([Category],[Category],[Sales])>SUMIF([Category],[@Category],[Sales])),--(MATCH([Category],[Category],0)=ROW([Category])-ROW($A$2)+1))+1

 

[Marcelo Branco]

You are welcome. Glad to help

Table style
=SUMPRODUCT(--(SUMIF([Category],[Category],[Sales])>SUMIF([Category],[@Category],[Sales])),--(MATCH([Category],[Category],0)=ROW([Category])-MIN(ROW([Category]))+1))+1

M.