Question: How do I rank text cell values uniquely? If text values were sorted alpabetically from A to Z, the first text value would rank 1 and so on... Duplicate text values rank uniquely.

Array formula in B2:

=COUNTIF(List, "<"&A2)+SUM(IF(A2=\$A\$2:A2, 1, 0))  + Enter

Copy cell B2 and paste  down as far as needed.

Named ranges
List
(A2:A13)

What is named ranges?

### How does this formula work?

=COUNTIF(List, "<"&A2)+SUM(IF(A2=\$A\$2:A2, 1, 0))

Step 1 - Calculate rank

=COUNTIF(List, "<"&A2)

becomes

=COUNTIF({DD, AA, AA, BB, AA, DD, EE, AA, BB, EE, CC, EE}, "<"&DD)

and returns 7. 7 values are sorted before DD, as if they were sorted from A to Z.

Step 2 - Count current value and prior instances of current value

Keep in mind, countif doesn´t rank uniquely if duplicate values are found.

SUM(IF(A2=\$A\$2:A2, 1, 0))

becomes

SUM(IF("DD"="DD", 1, 0))

becomes

SUM(1) and returns 1

Step 1 returned 7 and step 2 returned 1. 7 + 1 equals 8.

Select B3 and use "evaluate formula" to see how the next cell calculates rank. Click "Evaluate" button and excel calculates cell formula step by step.

text-values-uniquely-ranked.xls
(Excel 97-2003 Workbook *.xls)

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SUM(number1,[number2],)
Adds all the numbers in a range of cells

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

### Related posts:

How to rank uniquely with criteria in excel

How to create an array of uniquely ranked duplicate values in excel

Excel: How to rank by two columns

Create a unique distinct text list from a range containing both numerical and text values in excel

Extract and sort text cells from a range containing both numerical and text values