How to Rank Without Duplicating in Excel 2007
One common need in Excel is to rank and sort data. This can be problematic when the data you're ranking has duplicate values, which results in duplicate rankings. Making a ranking list without duplication requires combining the RANK() function with the COUNTIF() function.
Things You'll Need
- Excel 1995 or later
Creating the Formula
Start Excel and open up the data range you want to use. For this example, we're assuming that the data values to be ranked are in column A, starting in column A2.
Enter the following formula in cell B2: =RANK(A2,$A$2:$A$10)+COUNTIF($A$2:A2,A2)-1. This formula ranks the data, using the value in A2 as the seed value, going through the ranks from top to bottom. The COUNTIF in the second part of the formula increments the rank number by 1, EXCEPT if the rank value is the same.
Copy the formula in cell B2 through the entire range of cells from B3 to B10.
Tips & Warnings
- This is one example of how to use COUNTIF to modify the output of other functions that generate a numerical value, such as SUM, SUMIF, COUNT and COUNTA.