1. Unique distinct values sorted based on frequency

Array formula in D3:

copied down as far as needed.

How to create an array formula

1. Copy above array formula
2. Select cell D3
3. Press with left mouse button on in formula bar
4. Paste array formula in formula bar
5. Press and hold Ctrl + Shift
6. Press Enter

Formula in E3:

copied down as far as needed.

Explaining formula in cell D3

Step 1 - Calculate frequency of each value

The COUNTIF function counts values based on a condition, in this case, multiple conditions.

COUNTIF($B$3:$B$15, $B$3:$B$15)

becomes

COUNTIF({"DD"; "EE"; "GG"; "TT"; "EE"; "SS"; "YY"; "FF"; "GG"; "II"; "RR"; "TT"; "GG"}, {"DD"; "EE"; "GG"; "TT"; "EE"; "SS"; "YY"; "FF"; "GG"; "II"; "RR"; "TT"; "GG"})

and returns

{1; 2; 3; 2; 2; 1; 1; 1; 3; 1; 1; 2; 3}.

Step 2 - Prevent duplicate values

The first argument in the COUNTIF function contains an expanding cell reference. It makes sure that prior values are not taken into account again.

COUNTIF(D$2:$D2,$B$3:$B$15)<>1

becomes

COUNTIF("Unique distinct
list based on occurances", {"DD"; "EE"; "GG"; "TT"; "EE"; "SS"; "YY"; "FF"; "GG"; "II"; "RR"; "TT"; "GG"})<>1

becomes

{0;0;0;0;0;0;0;0;0;0;0;0;0}<>1

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.

Step 3 - Multiply arrays

COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2,$B$3:$B$15)<>1)

becomes

{1; 2; 3; 2; 2; 1; 1; 1; 3; 1; 1; 2; 3}* {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

and returns

{1;2;3;2;2;1;1;1;3;1;1;2;3}.

Step 4 - Check if largest value is equal to 0 (zero)

The IF function returns 1 if number is not equal to 0 (zero) and the largest value in array if equal to zero using the MAX function.

IF(MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>0))=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1)))

becomes

IF(MAX({1;2;3;2;2;1;1;1;3;1;1;2;3})=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1)))

becomes

IF(3=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1)))

becomes

IF(FALSE, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1)))

becomes

IF(FALSE, 1, 3)

and returns 3.

Step 5 - Find position in array

The MATCH function returns the position of a value in a cell range or array.

MATCH(IF(MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>0))=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1))), COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1), 0))

becomes

MATCH(3, COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1), 0)

becomes

MATCH(3, COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1), 0)

becomes

MATCH(3, {1;2;3;2;2;1;1;1;3;1;1;2;3}, 0)

and returns 3.

Step 6 - Return value

The INDEX function returns a value based on a row number (and column number if needed).

INDEX($B$3:$B$15, MATCH(IF(MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*COUNTIF(D$2:$D2,$B$3:$B$15)<>1)=0, 1, MAX(COUNTIF($B$3:$B$15, $B$3:$B$15)*IF(COUNTIF(D$2:$D2, $B$3:$B$15)=1,0,1))), COUNTIF($B$3:$B$15, $B$3:$B$15)*(COUNTIF(D$2:$D2, $B$3:$B$15)<>1), 0))

becomes

INDEX($B$3:$B$15, 3)

and returns "GG" in cell D3.

Back to top

Get Excel *.xlsx file

Unique-distinct-list-sorted-based-on-occurrance-in-a-column-in-excel.xlsx

2. Unique distinct values sorted based on frequency - Excel 365

This formula works only with a single column cell range, read this article if you want to use a multi-column cell range: Extract a unique distinct list across multiple columns and rows sorted based on frequency

Excel 365 dynamic array formula in cell D3:

Explaining formula

Step 1 - Extract unique distinct values

The UNIQUE function extracts both unique and unique distinct values and also compare columns to columns or rows to rows.

UNIQUE(array,[by_col],[exactly_once])

UNIQUE(B3:B15)

becomes

UNIQUE({"DD"; "EE"; "GG"; "TT"; "EE"; "SS"; "YY"; "FF"; "GG"; "II"; "RR"; "TT"; "GG"})

and returns

{"DD"; "EE"; "GG"; "TT"; "SS"; "YY"; "FF"; "II"; "RR"}

Step 2 - Count values

The COUNTIF function calculates the number of cells that meet a given condition.

COUNTIF(range, criteria)

COUNTIF(B3:B15,UNIQUE(B3:B15))

Step 3 - Sort values based on count

The SORTBY function sorts values from a cell range or array based on a corresponding cell range or array.

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

SORTBY(UNIQUE(B3:B15), COUNTIF(B3:B15,UNIQUE(B3:B15)),-1)

Step 4 - Shorten formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

SORTBY(UNIQUE(B3:B15), COUNTIF(B3:B15,UNIQUE(B3:B15)),-1)

x - B3:B15
y - UNIQUE(x)

LET(x,B3:B15,y,UNIQUE(x),SORTBY(y,COUNTIF(x,y),-1))

Back to top

---

Weekly Blog EMAIL

[newsletter_signup_form id=1]

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---

Extract a unique distinct list across multiple columns and rows sorted based on frequency

Cell range B2:E11 contains values, the formula in cell B15 extracts unique distinct values in B2:E11, ignores blanks, and returns […]

How to use the EXPAND function

The EXPAND function increases a cell range or array by a specified number of columns and rows. Dynamic array formula […]

Filter unique values sorted from A to Z

A unique value is a value that only exists once in a list. A unique distinct list contains all cell values […]

Count groups of repeated values per row

Joe asks: I have a worksheet that has rows, each containing sequential groupings of values of "1" and "0". These […]

Identify missing numbers in a range

Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]

Lookup and match last value – reverse lookup

This article shows a formula that performs a reverse lookup and returns the corresponding value based on the last matching […]