Question: How do I rank column B "within" the same category in column A? See picture below. I want the formula to rank text values uniquely.

rank-text-uniquely-with-criteria4

Answer: (Edit: Simplified excel formula and attached a new excel file.)

rank-text-uniquely-with-criteria3

Array formula in C2:

=SUM(IF((B2&ROW()>Text_cells&ROW(Text_cells))*(A2=ID), 1, 0))+1

How to create an array formula

  1. Select cell C2
  2. Type array formula in cell C2
  3. Press and hold Ctrl + Shift
  4. Press enter once
  5. Release all keys

Copy formula down to C6

Named ranges
ID
(A2:A6)
Text
_cells (B2:B6)
What is named ranges?

Download excel example fil
ranking-text with criteria.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

ROW(reference) Returns the rownumber of a reference

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