Author: Oscar Cronquist Article last updated on November 18, 2018

The following formula ranks text values in column C uniquely based on the category in column B.

Formula in D3:

=SUMPRODUCT((C3&ROW()>$C$3:$C$9&ROW($C$3:$C$9))*(B3=$B$3:$B$9))+1

Explaining formula in cell D3

Step 1 - Concatenate cell C3 and current row number

The ROW function returns the row number from a cell reference, if a cell ref is omitted then the row number of the current cell is returned.

The ampersand character & concatenates two values.

C3&ROW()

becomes

"parta"&3

and returns parta3.

Step - 2 Concatenate each cell in column C and corresponding row numbers row-wise

$C$3:$C$9&ROW($C$3:$C$9)

becomes

{"parta";"partb";"partb";"parta";"partc";"parta";"partb"}&{3;4;5;6;7;8;9}

and returns

{"parta3";"partb4";"partb5";"parta6";"partc7";"parta8";"partb9"}

Step 3 - Compare value with array using the larger than sign

(C3&ROW()>$C$3:$C$9&ROW($C$3:$C$9))

becomes

parta3>{"parta3";"partb4";"partb5";"parta6";"partc7";"parta8";"partb9"}

and returns

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.

The number concatenated to each value makes it unique.

Step 4 - Compare categories with current cell

(B3=$B$3:$B$9)

becomes

7={7;7;7;11;11;7;11}

and returns

{TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}

Step - 5 Multiply arrays

(C3&ROW()>$C$3:$C$9&ROW($C$3:$C$9))*(B3=$B$3:$B$9)

becomes

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*{TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}

and returns

{0;0;0;0;0;0;0}

Step 6 - Sum values in array

SUMPRODUCT((C3&ROW()>$C$3:$C$9&ROW($C$3:$C$9))*(B3=$B$3:$B$9))

becomes

SUMPRODUCT({0;0;0;0;0;0;0})

and returns 0.

Step 7 - Add 1

SUMPRODUCT((C3&ROW()>$C$3:$C$9&ROW($C$3:$C$9))*(B3=$B$3:$B$9)) + 1

becomes

0 + 1 equals 1 in cell D3.

Download Excel *.xlsx file

ranking text with condition.xlsx