How to Generate a Special Ranking List in Excel

A ranking list is a common way to compare and order items based on some criteria, such as sales, scores, ratings, etc. In Excel, you can use the RANK function to assign a rank to each item in a range, based on its value. For example, you can rank the sales of different products from highest to lowest, or the scores of students from lowest to highest.

However, sometimes you may want to generate a special ranking list, where you can apply some custom rules or conditions to the ranking process. For example, you may want to rank the products by sales, but only include the products that have a positive profit margin. Or, you may want to rank the students by scores, but only include the students that passed the exam. Or, you may want to rank the items by value, but group them by category and rank them within each group.

In this article, we will show you how to generate a special ranking list in Excel, using different methods and formulas. We will also explain the basic theory and the procedures behind each method, and provide a detailed example with real data. We will use an Excel table to illustrate the steps and the results.

Method 1: Using IF and RANK Functions

One way to generate a special ranking list in Excel is to use the IF and RANK functions together. The IF function allows you to test a condition and return a value based on whether the condition is true or false. The RANK function allows you to assign a rank to a value in a range, based on its order.

The basic syntax of the IF function is:

=IF(logical_test, value_if_true, value_if_false)

The basic syntax of the RANK function is:

=RANK(number, ref, [order])

To use these functions together, you can use the IF function to test the condition that you want to apply to the ranking process, and return the RANK function as the value_if_true argument. For the value_if_false argument, you can return a blank value, an error value, or any other value that you want to display for the items that do not meet the condition.

For example, suppose you have a table of products and their sales and profit margin, as shown below:

You want to rank the products by sales, but only include the products that have a positive profit margin. To do this, you can use the following formula in cell D2 and copy it down:

=IF(C2>0,RANK(B2,$B$2:$B$6,0),"")

This formula tests if the profit margin in cell C2 is greater than 0, and if so, returns the rank of the sales in cell B2 within the range B2:B6, in descending order (0 means descending, 1 means ascending). If the profit margin is not greater than 0, the formula returns a blank value (“”).

The result is shown below:

As you can see, the formula only ranks the products that have a positive profit margin, and leaves the others blank.

You can modify the formula to suit your needs, by changing the condition, the range, the order, or the value_if_false argument. For example, if you want to rank the products by profit margin instead of sales, you can use the following formula:

=IF(C2>0,RANK(C2,$C$2:$C$6,1),"")

This formula ranks the profit margin in cell C2 within the range C2:C6, in ascending order (1 means ascending, 0 means descending).

The result is shown below:

Method 2: Using FILTER and RANK Functions

Another way to generate a special ranking list in Excel is to use the FILTER and RANK functions together. The FILTER function allows you to filter a range based on one or more criteria, and return the filtered values in a new range. The RANK function allows you to assign a rank to a value in a range, based on its order.

The basic syntax of the FILTER function is:

=FILTER(array, include, [if_empty])

The basic syntax of the RANK function is:

=RANK(number, ref, [order])

To use these functions together, you can use the FILTER function to filter the range that you want to rank, based on the condition that you want to apply to the ranking process. Then, you can use the RANK function to assign a rank to each value in the filtered range, based on its order.

For example, suppose you have the same table of products and their sales and profit margin, as shown below:

You want to rank the products by sales, but only include the products that have a positive profit margin. To do this, you can use the following formula in cell D2 and copy it down:

=RANK(B2,FILTER($B$2:$B$6,$C$2:$C$6>0),0)

This formula filters the sales range B2:B6, based on the condition that the profit margin range C2:C6 is greater than 0. Then, it ranks the sales in cell B2 within the filtered range, in descending order (0 means descending, 1 means ascending).

The result is shown below:

As you can see, the formula only ranks the products that have a positive profit margin, and returns an error value (#N/A) for the others.

You can modify the formula to suit your needs, by changing the condition, the range, the order, or the if_empty argument. For example, if you want to rank the products by profit margin instead of sales, you can use the following formula:

=RANK(C2,FILTER($C$2:$C$6,$C$2:$C$6>0),1)

This formula filters and ranks the profit margin range C2:C6, in ascending order (1 means ascending, 0 means descending).

The result is shown below:

Method 3: Using SUMPRODUCT and COUNTIF Functions

A third way to generate a special ranking list in Excel is to use the SUMPRODUCT and COUNTIF functions together. The SUMPRODUCT function allows you to multiply corresponding elements in one or more arrays, and return the sum of the products. The COUNTIF function allows you to count the number of cells in a range that meet a certain criterion.

The basic syntax of the SUMPRODUCT function is:

=SUMPRODUCT(array1, [array2], [array3], ...)

The basic syntax of the COUNTIF function is:

=COUNTIF(range, criterion)

To use these functions together, you can use the SUMPRODUCT function to multiply two arrays: one that contains the values that you want to rank, and another that contains the condition that you want to apply to the ranking process. Then, you can use the COUNTIF function to count the number of values that are greater than or equal to the current value, within the same range.

For example, suppose you have the same table of products and their sales and profit margin, as shown below:

You want to rank the products by sales, but only include the products that have a positive profit margin. To do this, you can use the following formula in cell D2 and copy it down:

=SUMPRODUCT((B2<=$B$2:$B$6)*(C2>0)*(C2<=$C$2:$C$6))

This formula multiplies three arrays: the first one contains the sales in cell B2, compared to the sales range B2:B6 using the less than or equal to operator (<=); the second one contains the condition that the profit margin in cell C2 is greater than 0; and the third one contains the profit margin in cell C2, compared to the profit margin range C2:C6 using the less than or equal to operator (<=). The result is the sum of the products of these arrays, which is the number of values that meet the condition and are greater than or equal to the current value.

The result is shown below:

As you can see, the formula only ranks the products that have a positive profit margin, and returns 0 for the others.

You can modify the formula to suit your needs, by changing the condition, the range, the order, or the operators. For example, if you want to rank the products by profit margin instead of sales, you can use the following formula:

=SUMPRODUCT((C2>=$C$2:$C$6)*(C2>0)*(C2>=$C$2:$C$6))

This formula multiplies three arrays: the first one contains the profit margin in cell C2, compared to the profit margin range C2:C6 using the greater than or equal to operator (>=); the second one contains the condition that the profit margin in cell C2 is greater than 0; and the third one contains the profit margin in cell C2, compared to the profit margin range C2:C6 using the greater than or equal to operator (>=). The result is the sum of the products of these arrays, which is the number of values that meet the condition and are greater than or equal to the current value.

The result is shown below: