< Go Back

Basic numeric sort formula

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.

Syntax

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.

=COUNTIF(range,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.

=RANK(number,ref[order])

Explanation

In this case, we’ll use a mixture of both COUNTIF and RANK function to dynamically sort the numeric data.

Generic formula

=RANK(A1,values)+COUNTIF(exp_rng,A1)-1

Example

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

=RANK(C5,sales)+COUNTIF($C$5:C5,C5)-1

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.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar