With a **helper column**, we can use a combination of the **RANK** and **COUNTIF Functions** to dynamically **sort data **that contains only numeric values. The steps below will walk through the process.

*Figure 1: Basic Numeric Sort Formula*

**Syntax**

**=COUNTIF(range,criteria)**

**COUNTIF**count cells with numbers, dates and certain text that match specific**criteria**in a**range.**

**=RANK(number,ref[order])****RANK function**is used to determine the position of a value in an array.

**Formula**

`=RANK(B4,Cost)+COUNTIF($B$4:B4,B4)-1`

**Setting up the Data**

- We will set up the data by inputting the
**items**into**Column A** - We will input the
**cost of each item**into**Column B** **Column C**is where we want the formula to return the result of the sorted data**Note-**We must highlight**Cell B4 to B11**and name it as**Cost**by clicking on**B3**where there is a drop-down arrow in**figure 2**

* Figure 2: Setting up the Data*

**Numerical Sorting of the Data**

- We will click on
**Cell C4** - We will insert the formula below into the cell

**=RANK(B4,Cost)+COUNTIF($B$4:B4,B4)-1** `We will`

**press the enter key**

* Figure 3: Result for Cell B4*

- We will click on
**Cell C4**again - We will
**double-click**on the fill handle (**the small plus sign at the bottom right of Cell C4**) and drag down to copy the formula into the other cells

*Figure 4: Results of basic numeric sort formula*

**Explanation**

`=RANK(B4,Cost)+COUNTIF($B$4:B4,B4)-1`

**RANK** uses the **named range (Cost)** to generate values from the highest to lowest starting with **1**.

**COUNTIF** is required if there are **duplicate values**. A mixed reference is used to enter the **range** so that the formula **continuously expands** when copied to the other cells. We have to subtract 1 to **zero out** this effect. The formula, therefore, returns **zero** until a duplicate value is found and returns** 1** at the second instance.

**Instant Connection to an Expert through our Excelchat Service**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment