Ranking with multiple of the same name in one column

[mwong1782]

Hello, I am looking to fix my formula or understand a better formula to rank titles in one column where the title can appear multiple times with different number of units each time the title appears. Below is an example (Note I only included the rank in column C without the name of the title to make it easier to read):

ColumnA ColumnB Column C - Ranking Column D Formula
Title A 517 2 =$A2&COUNTIFS($A:$A,$A2,$B:$B,">"&$B2)+COUNTIFS($B$2:$B2,$B2)
Title A 4706 1 =$A3&COUNTIFS($A:$A,$A3,$B:$B,">"&$B3)+COUNTIFS($B$2:$B3,$B3)
Title B 628 3 =$A4&COUNTIFS($A:$A,$A4,$B:$B,">"&$B4)+COUNTIFS($B$2:$B4,$B4)
Title B 2454 1 =$A5&COUNTIFS($A:$A,$A5,$B:$B,">"&$B5)+COUNTIFS($B$2:$B5,$B5)
Title B 21 6 =$A6&COUNTIFS($A:$A,$A6,$B:$B,">"&$B6)+COUNTIFS($B$2:$B6,$B6)
Title B 210 4 =$A7&COUNTIFS($A:$A,$A7,$B:$B,">"&$B7)+COUNTIFS($B$2:$B7,$B7)
Title B 132 5 =$A8&COUNTIFS($A:$A,$A8,$B:$B,">"&$B8)+COUNTIFS($B$2:$B8,$B8)
Title B 778 2 =$A9&COUNTIFS($A:$A,$A9,$B:$B,">"&$B9)+COUNTIFS($B$2:$B9,$B9)

What I would like is for column C to include the title and the ranking based on the sum of column B. For instance, I want Title A to be ranked 1 on both row 2 and 3 in column C. I also want Title B to be ranked 2 in the six columns it appears.

Any help will be appreciated.
Thank you in advance.

 

[XLearner]

Hi,

Assuming data in A2:B17 with no empty row. In C2:

=A2 &" " &RANK(MAX(($A$2:$A$17=A2)*$B$2:$B$17),$B$2:$B$17,0)​

validate with [Ctrl]+[Shift]+[Enter] (array formula) and copy down until row 17

If you pref. a non-array formula. In C2:

=A2 &" " &RANK(SUMPRODUCT(MAX(($A$2:$A$17=A2)*$B$2:$B$17)),$B$2:$B$17,0)<strike></strike>

​

validate with [Enter] and copy down until row 17

Regards
XLearner

 

[mwong1782]

Thanks XLEarner it worked perfectly.

Manny

Hi,

Assuming data in A2:B17 with no empty row. In C2:

=A2 &" " &RANK(MAX(($A$2:$A$17=A2)*$B$2:$B$17),$B$2:$B$17,0)​

validate with [Ctrl]+[Shift]+[Enter] (array formula) and copy down until row 17

If you pref. a non-array formula. In C2:

=A2 &" " &RANK(SUMPRODUCT(MAX(($A$2:$A$17=A2)*$B$2:$B$17)),$B$2:$B$17,0)<strike></strike>

​

validate with [Enter] and copy down until row 17

Regards
XLearner

 

[mwong1782]

Hello XLearner,
Sorry but the formula is ranking the max unit by title but I want it to rank based on the total sum of all the rows the title appears.
I used the 'non=array' formula on my workbook and found it was not doing exactly what I needed.

Any added help will be appreciated.
Thanks,
Manny

 

[XLearner]

Hi,

I'm stil unsure this is what you expect... The following requires an extra column (C).

In C2:=IF(ISNA(MATCH(SUMPRODUCT(--($A$2:$A$17=$A2),$B$2:$B$17),$C$1:$C1,0)),SUMPRODUCT(--($A$2:$A$17=$A2),$B$2:$B$17))
validate with [Ctrl]+[Shift]+[Enter] as this is an array formula. Then copy it down as necessary (until row 17 with this formula)

In D2:
=A2 &" " &RANK(SUMPRODUCT(--($A$2:$A$17=$A2),$B$2:$B$17),$C$2:$C$17,0)
validate with [Enter]. Then copy it down as necessary (until row 17 with this formula)

Given that RANK doesn't accept an array as 2nd argument I don't see how to avoid this extra column...
Also, not sure how robust this proposal is for you as if the SUM of 2 different Titles (say Title A and Title D) is the same Titles will be ranked the same

Regards
XLearner