Ranking numbers in Excel using a formula is straightforward. Sorting text alphabetically is trickier. Ranking a larger data set alphabetically using multiple text criteria (i.e. “rank A-Z by Column A, then by Column B”) is fraught with difficulties. This tutorial will cover how to rank by multiple criteria in Excel.
Excel’s built-in ‘Sort’ function provides a quick and easy way of sorting any data set. However, there are some occasions where the sort needs to be automatic (e.g. when dealing with linked cells or arrays). In those situations, you’ll need a formula. This blog will detail how to write a formula that automatically ranks and orders data within Excel.
Part 1 : The Input
This process assumes your document has a raw data input, where the data is entered in any order; and an automated output, which displays the data ranked alphabetically.
A typical input might be a data table of staff, displaying their names and departments, as below.
For this example, we will organise the data firstly by Department (Column B), then by Surname (Column D).
Using COUNTIF() as an alternative to RANK()
The first thing we need to do is identify the alphabetical rank of the data in each of the relevant columns. Excel’s RANK() function only works with numerical data, so we cannot rely on it for this task. Instead, we will use COUNTIF().
Whenever you rank by multiple criteria in Excel, you will need to use a COUNTIF() or COUNTIFS() expression. So let’s start by looking at that.
The COUNTIF() function requires a range and a condition. We will set the range as column B, as this is what we wish to rank. For the condition, we can take advantage of the fact that COUNTIF()’s “greater than (>)” and “less than (<)” operations apply to text entries as well as numerical values. We want to tell the formula to count the number of entries in the defined range that are alphabetically earlier than the value in the current row.
Note that the “<” symbol is contained within quote marks and accompanied by an ampersand (&). This is the notation required by the COUNTIF() function.
Cell F3 displays ‘5’ because there are 5 entries in the defined range that are alphabetically earlier than the value in cell B3. Dragging this formula down results in the following outcomes:
This will ensure that all ‘Marketing’ entries are ranked ahead of all ‘Sales’ entries. Next, we need to apply a similar ranking mechanism to the data in column D. However, our requirements are slightly different for this column because the ranking needs to take account of the department associated with each entry.
Rank by Multiple Criteria in Excel using COUNTIFS()
We therefore need to add a second condition to our formula, which will only count the surnames in the relevant department for each row of data. This requires a COUNTIFS() function. We will set this up in a separate ranking column initially, as below:
This formula is counting the number of entries in column D that are alphabetically earlier than the value in the current row, but only if the entry in column B matches the value in the current row.
For example, “Tom Draper” (row 9) has the alphabetically earliest surname in the ‘Sales’ department. “George Parsons” (row 6) is the 4th ranked entry (i.e. 3 surnames are alphabetically earlier than his) in the ‘Marketing’ department.
Adding the two values in columns F and G together produces a series of unique numbers that can be used as a ranking. However, the earliest combination of department and surname (row 3) gives an outcome of ‘0’. This is usually fine, but to avoid any confusion caused by having blanks in the defined range, it is best to start the ranking from ‘1’.
We therefore want to produce a formula that comprises the following:
- ‘Formula in column F’ + ‘Formula in column G’ + 1
This allows us to express the alphabetical ranking in a single column, as below:
You can see that column F now ranks the data according to our pre-defined criteria – i.e. firstly by column B (all individuals in ‘Marketing’ are ranked before all individuals in ‘Sales’) and secondly by column D.
Formula for Part 1
This formula uses the COUNTIF() and COUNTIFS() functions to rank by multiple criteria in Excel. It calculates using the following steps:-
Count all entries in the defined range that are alphabetically earlier than the value in the current row
COUNTIFS() First Condition
Take account of only the entries in the defined range that are the same as the value in the current row
COUNTIFS() Second Condition
Count all entries in the defined range that are alphabetically earlier than the value in the current row. Add 1
Get The Formula
The full formula used in Part 1 of this blog (Rank by Multiple Criteria in Excel) is as follows:-
Part 2 : The Output
Establishing a ranking order for the data is only half the task. If you’re needing to use a formula to rank by multiple criteria in Excel, it’s likely that Excel’s Auto Sort function isn’t an option for your output. This section will explain how to set up an output table that automatically draws data from the input table and displays it in the defined order.
You can locate the output table anywhere in the workbook. In this example, it will be on the same tab, 20 rows beneath the input table.
When we created the ranking column in Part 1, we established a set of unique identifiers for each row of the data, which can be used as lookup values. We can use an INDEX(MATCH()) combo to lookup the desired value and return the associated data.
For a more detailed look at INDEX(MATCH()) formulas, check out my blog post Why INDEX-MATCH is better than VLOOKUP.
In the first row of the table, we want to display the data that is associated with the value ‘1’ (i.e. the row of data in the input table that is ranked first alphabetically). We can achieve this using the formula below:
Dragging this formula down would display the same data for every row because the MATCH() function would always be searching for a lookup value of ‘1’. We therefore need a function that dynamically adjusts the lookup value to match the row of the table where the formula is located (e.g. row 2 would search for a lookup value of ‘2’, etc.)
Using ROWS() to define a lookup value
The ROWS() counts the number of rows in a defined range, for example:
This formula returns a value of ‘8’ because there are 8 rows in the defined range (outlined in blue).
The formula =ROWS(B$23:B23) would return a value of ‘1’, which would allow us to pull in the first row of data. Notice that the first part of the range is locked (using the ‘$’ sign), whereas the second part is not. When this formula is dragged down, the first part of the range will always refer to the first row of the table. The second part of the range will always refer to the current row of the table. This creates a dynamic lookup value based on the current row of the table, as below:
This output table is now linked to the input table, so any changes to the input data (for example, switching “Tony Link” to the Sales department) are reflected in the output table automatically.
If you’ve stuck with this tutorial to the bitter end, congratulations! You can now rank by multiple criteria in Excel.
Download The Workbook
This formula is an INDEX(MATCH()) combo with a nested ROWS() function. It calculates using the following steps:-
Defines the lookup range
Defines the lookup value, i.e. the rank, dynamically based on the current row of the table
Defines the range where the lookup value can be found, i.e. the ‘Rank’ column created in Part 1
Get The Formula
The full formula used in Part 2 of this blog is as follows:-