Article updated on July 30, 2017

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.


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


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
_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

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Adds all the numbers in a range of cells