## Sort a range by occurence using array formula in excel

**Question:** How do I sort a range containing multiple columns and rows by occurence?

**Answer: **The range is sorted by occurrence from large to small. See picture below.

**Array formula in B9:**

copied down as far as necessary.

Recommended articles

**Create a unique distinct list and sort by occurrances from large to small**

Comments(15) Filed in category: Excel

**How to count unique distinct occurrences for each date in excel**

Comments(4) Filed in category: Count values, Excel, Unique distinct values

**Count text that occurs multiple times in excel cell**

Comments(9) Filed in category: Count text values, Excel

**Count occurences of a specific text string in a column in excel**

Comments(9) Filed in category: Count text values, Count values, Excel

**Unique distinct list sorted based on occurrance in a column in excel**

Comments(0) Filed in category: Excel

**Named ranges**

tbl (B3:E6)

What is named ranges?

### Download excel sample file for this tutorial

Sort a range by occurences.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**IF(**logical_test;[value_if:true],[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**NOT(**logical**)**

Changes FALSE to TRUE or TRUE to FALSE

**MATCH(**lookup_value;lookup_array; [match_type]

Returns the relative position of an item in an array that matches a specified value

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

**ROWS(**array**)** returns the number of rows in a reference or an array

**MAX(**number1,[number2],**)
**Returns the largest value in a set of values. Ignores logical values and text.

### Category: Sort values

Comments(81) Filed in category: Excel, Sort values

Sort dates within a date range using excel array formula

Question: I have a list of unsorted dates. I want to extract records between two dates and I want the […]Comments(23) Filed in category: Excel, Sort values

Extract unique distinct values from a filtered table (udf and array formula)

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]Comments(17) Filed in category: Excel, Sort values, Unique distinct values

Sort values in parallel (array formula)

Table of contents How to sort a table by Column 1 and then by Column 2 (array formula) How to […]Comments(16) Filed in category: Excel, Sort values

Lookup and return multiple values sorted in a custom order

Pat asks: Hi Oscar, Thanks for creating such a helpful website and I've a question if I would like to […]Comments(13) Filed in category: Excel, Sort values, Vlookup

Sort text cells alphabetically from two columns using excel array formula

Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]Comments(13) Filed in category: Excel, Sort values

How to sort cells filtered by two dates

Question: I have a list containg dates and values. How do I sort values between two specific dates? Answer: Yellow […]Comments(12) Filed in category: Dates, Excel, Sort values

Comments(7) Filed in category: Excel, Mod, Sort values

Comments(7) Filed in category: Excel, Sort values

Sort values in a cell using a custom delimiter (vba)

The following macro let´s you select a cell range and a delimiting character. The macro sorts the values in each […]Comments(5) Filed in category: Excel, Sort values

### 4 Responses to “Sort a range by occurence using array formula in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Your array formulas are very interesting.

But this fails if there is two or more values whith the same frequency.

Many thanks and best regards

Thanks for your comment! I have changed the formula and the attached excel file. The formula doesn´t work with blank cells.

Hi, very nice formula! I´m trying to do something like this, but I need to show one more column at the side of each unique element with the count of occurrences :-)

Fernando,

In the above example, try this formula in C9 copied down as far as necessary.

=COUNTIF(tbl, B9) + CTRL + SHIFT + ENTER