At times, we are required to sort a large amount of data. In excel we can easily execute the task of sorting the data containing numeric values only by creating a formula with RANK and COUNTIF function.
COUNTIF is an inbuilt function of MS Excel that is used to count the number of cells that meet any given criteria. It can be used to count cells with numbers, dates and certain text that match specific criteria.
The RANK function is another inbuilt function of Excel that determines the position or rank of a value in an array. We can use this function to compare a number to other numbers in the same list and determine its position.
In this case, we’ll use a mixture of both COUNTIF and RANK function to dynamically sort the numeric data.
In the example shown, there are three columns. One contains items name, other contains sales value and the third column is used to display the result of sorting the data.
To sort the values, we apply the formula in D5
Here, “sales” is the named range from C5:C11
Figure 1: Illustration of dynamic sort
This formula displays the result 4 in cell D5 as sales of $700 is the 4th highest value. Similarly, we can generate the sort result in each column by dragging the formula. The results so displayed are shown in the figure below:
Figure 2: Results of basic numeric sort formula
In the figure, you can notice that 1 is displayed for boots in D6 as it has the highest sales i.e. $1500.
As long as the numeric values are different from each other, the rank function will assign 1 to the highest value, 2 to the second highest value and so on.