Author: Oscar Cronquist Article last updated on November 10, 2021

Sort column based on frequency1

This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value is repeated in the list.

1. Sort column based on the frequency

Sort column based on frequency1

Question:

How do I create a new unique distinct list from a column? I also want the list sorted from large to small by the number of times a value is repeated?

Answer:

Array formula in cell D3:

=INDEX($B$3:$B$14, MATCH(LARGE(IF(COUNTIF($D$2:D2, $B$3:$B$14)=0, COUNTIF($B$3:$B$14, $B$3:$B$14), ""), 1), COUNTIF($B$3:$B$14, $B$3:$B$14)*(COUNTIF($D$2:D2, $B$3:$B$14)=0), 0))

Back to top

1.1 How to create an array formula

  1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar. See the picture below.
  2. Press and hold Ctrl + Shift.
  3. Press Enter once.
  4. Release all keys.

Recommended article

Recommended articles

A beginners guide to Excel array formulas
Array formulas allows you to do advanced calculations not possible with regular formulas.

Back to top

How to copy array formula in cell D3

  1. Select cell D3
  2. Copy cell (Ctrl + c)
  3. Select cell range D4:D8
  4. Paste (Ctrl + v)

Back to top

1.2 Explaining array formula in cell D3

Step 1 - Count previous items

The COUNTIF function calculates the number of cells that is equal to a condition.

COUNTIF(rangecriteria)

COUNTIF($D$2:D2, B3:B14)

becomes

COUNTIF("List sorted by frequency", {"DD";"AA";"AA";"BB";"AA";"DD";"EE";"AA";"BB";"EE";"CC";"EE"})

and returns

{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}.

Step 2 - Identify positions of previous items

The equal sign compares each value in the array to 0 (zero), the result is a boolean value TRUE or FALSE.

COUNTIF($D$2:D2, B3:B14)=0

becomes

{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}=0

and returns

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

Step 3 - Count items in the list

COUNTIF(B3:B14, B3:B14)

becomes

COUNTIF({"DD"; "AA"; "AA"; "BB"; "AA"; "DD"; "EE"; "AA"; "BB"; "EE"; "CC"; "EE"}, {"DD"; "AA"; "AA"; "BB"; "AA"; "DD"; "EE"; "AA"; "BB"; "EE"; "CC"; "EE"})

and returns

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

Step 4 - Create array containing count for new values

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF(COUNTIF($D$2:D2, B3:B14)=0, COUNTIF(B3:B14, B3:B14), "")

becomes

IF({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}, {2; 4; 4; 2; 4; 2; 3; 4; 2; 3; 1; 3}, "")

and returns

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

Step 5 - Get largest count

The LARGE function calculates the k-th largest value from an array of numbers.

LARGE(arrayk)

LARGE(IF(COUNTIF($D$2:D2, B3:B14)=0, COUNTIF(B3:B14, B3:B14), ""), 1)

becomes

LARGE({2; 4; 4; 2; 4; 2; 3; 4; 2; 3; 1; 3}, 1)

and returns 4.

Step 5 - Find the position in the array of the largest count

The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

MATCH(lookup_valuelookup_array, [match_type])

MATCH(LARGE(IF(COUNTIF($D$2:D2, B3:B14)=0, COUNTIF(B3:B14, B3:B14), ""), 1), COUNTIF(B3:B14, B3:B14)*(COUNTIF($D$2:D2, B3:B14)=0), 0)

becomes

MATCH(4, COUNTIF(B3:B14, B3:B14)*(COUNTIF($D$2:D2, B3:B14)=0), 0)

becomes

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

and returns 2.

Step 6 - Get value based on position

The INDEX function returns a value from a cell range, you specify which value based on a row and column number.

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

INDEX(B3:B14, MATCH(LARGE(IF(COUNTIF($D$2:D2, B3:B14)=0, COUNTIF(B3:B14, B3:B14), ""), 1), COUNTIF(B3:B14, B3:B14)*(COUNTIF($D$2:D2, B3:B14)=0), 0))

becomes

INDEX(B3:B14, 2)

becomes

INDEX({"DD";"AA";"AA";"BB";"AA";"DD"; "EE";"VV";"BB";"EE";"VV";"EE"}, 2)

and returns "AA" in cell D3.

Sort column based on frequency1

Back to top

The following formula returns the count of the corresponding value in column D.

Formula in E3:

=COUNTIF($B$3:$B$14, D3)

Recommended articles

How to use the COUNTIF function
Counts the number of cells that meet a specific condition.

Back to top

2. Sort column by frequency - Excel 365

Sort column based on frequency

Formula in cell D3:

=UNIQUE(SORTBY(B3:B14,COUNTIF(B3:B14,B3:B14),-1))

Back to top

2.1 Explaining formula in cell D3

Step 1 - Count each value

The COUNTIF function calculates the number of cells that is equal to a condition.

COUNTIF(rangecriteria)

COUNTIF(B3:B14, B3:B14)

becomes

COUNTIF({"DD"; "AA"; "AA"; "BB"; "AA"; "DD"; "EE"; "AA"; "BB"; "EE"; "CC"; "EE"}, {"DD"; "AA"; "AA"; "BB"; "AA"; "DD"; "EE"; "AA"; "BB"; "EE"; "CC"; "EE"})

and returns

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

Step 2 - Sort values by count

The SORTBY function allows you to sort values from a cell range or array based on a corresponding cell range or array. It sorts values by column but keeps rows.

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

SORTBY(B3:B14, COUNTIF(B3:B14, B3:B14), -1)

becomes

SORTBY(B3:B14, {2; 4; 4; 2; 4; 2; 3; 4; 2; 3; 1; 3}, -1)

becomes

SORTBY({"DD"; "AA"; "AA"; "BB"; "AA"; "DD"; "EE"; "AA"; "BB"; "EE"; "CC"; "EE"}, {2; 4; 4; 2; 4; 2; 3; 4; 2; 3; 1; 3}, -1)

and returns

{"AA"; "AA"; "AA"; "AA"; "EE"; "EE"; "EE"; "DD"; "BB"; "DD"; "BB"; "CC"}.

Step 3 - Filter unique distinct values

The UNIQUE function extracts unique distinct rows from the array.

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

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

becomes

UNIQUE({"AA"; "AA"; "AA"; "AA"; "EE"; "EE"; "EE"; "DD"; "BB"; "DD"; "BB"; "CC"})

and returns {"AA"; "EE"; "DD"; "BB"; "CC"}.

Back to top

3. Get Excel example file

Back to top