How to rank uniquely with criteria in excel
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:
How to create an array formula
- Select cell C2
- Type array formula in cell C2
- Press and hold Ctrl + Shift
- Press enter once
- 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
Related posts:
How to rank text uniquely in excel
How to create an array of uniquely ranked duplicate values in excel
Excel: How to rank by two columns
Extract all rows from a range that meet criteria in one column in excel
Lookup two index columns using min max values and a date range as criteria
Recommended categories
I got the formula working, and although it's a great formula for criteria ranking it's not suitable for excel sheets with more than a thousand lines. Unless you have a very powerful machine.