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.

Answer:

text-values-uniquely-ranked

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?

Let us start with cell B2.

=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 3 - Add values

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

Final comments

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.

Download excel example file.

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

Functions in this article:

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