To sort by the number of occurrences in Google Sheets, we can adopt two different methods. One is by using the Data menu Sort Range and the other one is by using the SORT function.

The first method sorts the data in its own range but it requires a helper column. The latter (the SORT function) sorts and returns the output in a new range but no helper column is required.

Which one do you prefer?

In this post, irrespective of your preferences, you can learn the above two methods to sort a list or sort items by their number of occurrences (frequency of items in a list) in Google Sheets.

Where Do These Kinds of Sorting Come in Use?

First of all, let me make it clear that these kinds of sorting are not required in summary reports. Here is one scenario that would explain where we can use sort by the number of occurrences in Google Sheets.

The Scenario and Sample Data

If you sell different items and each transaction is recorded in a Google Sheets file, if you sort the items or product IDs by the number of their occurrences, you will get the most frequently selling (may not be the top-selling) items on the top.

In real life, I can relate this example to the sale of crusher products in fixed quantities in trucks.

Assume we have trucks with a loading capacity of 45 m³ each and we use these vehicles for transporting different crusher products such as sand, gravel, aggregate, etc. to customers.

If we sort the truck numbers by their frequency of transportation in descending order, we would get the trucks with the most number of trips on the top.

Similarly, if we sort the items (sand, gravel, etc.) by their number of occurrences in descending order, we would get the most frequently selling items on the top.

Note: By changing the sort order from descending to ascending (Z-A to A-Z) we can get the least selling items or less used vehicles for transportation on the top.

The good thing is that to sort by the number of occurrences in Google Sheets, we can use the SORT function without a frequency (occurrence) column.

Here is my sample data showing the transportation of sand and gravels in 45 cubic meter capacity trucks.

Formula to Sort by Number of Occurrences in Google Sheets

There are three columns in the list (dataset) and that is in the range A1:C11.

The number of rows in use might increase in the future. So I will use A1:C instead of A1:C11 as the range in my formulas.

Sorting the “item” or “truck number” column with the SORT function is not enough to sort the list by the number of occurrences of “item” or “truck number”.

Here is my required formulas and the formula explanation follows.

Formula # 1 (in cell E2):

Sort the List in A2:C by the Number of Occurrences of “Items”.

=sort(
     A2:C,
     if(len(A2:A),countif(A2:A,A2:A),),
     0,
     1,
     1
)

Formula # 2 (in Cell I2):

Sort the List in A2:C by the Number of Occurrences of “Truck Numbers”.

=sort(
     A2:C,
     if(len(A2:A),countif(B2:B,B2:B),),
     0,
     2,
     1
)

Formula Explanation

The formulas are actually quite simple if we try to understand the logical parts. The logical parts are as follows.

In formula # 1;

if(len(A2:A),countif(A2:A,A2:A),)

In formula # 2,

if(len(A2:A),countif(B2:B,B2:B),)

I’ll first explain the purpose of these formula parts in sort by the number of occurrences in Google Sheets. Then we can go to the other parts (parameters) of the formula.

Actually the formula parts are for returning the occurrences of the “items” (A2:A) in formula # 1 and the occurrences of “truck numbers” (B2:B) in the formula # 2.

Let’s test either of the above formulas.

So, in cell D2, enter the first IF + COUNTIF logical part. On contrary to our expectation, it may return a single value (count) in cell D2. Why?

It’s because the IF and COUNTIF are non-array formulas, so it won’t expand the count.

But we have used the same inside the SORT function in formula # 1 as well as in formula # 2. The SORT is an array formula, so it forces the logical part to expand.

We have taken out the logical part for testing in cell D2. So we should include the ArrayFormula (to compensate the SORT) to get an expanding result.

For example, the below formula will return the frequency of the “items” (crusher products).

=ArrayFormula(
     IF(len(A2:A),countif(A2:A,A2:A),)
)

In formula # 1, we have sorted the list A2:C based on the above formula in descending order.

Here, once again, see the sort by the number of occurrences formula.

=sort(
     A2:C,
     if(len(A2:A),countif(A2:A,A2:A),),
     0,
     1,
     1
)

And also the SORT syntax.

SORT(range, sort_column, is_ascending, [sort_column2, …], [is_ascending2, …])

Let’s try to understand each arguments that we have used in the formula now.

range: A2:C
sort_column: if(len(A2:A),countif(A2:A,A2:A),)
is_ascending: 0 (means sort sort_column in descending order)
sort_column2: 1 (the column contains the “item”)
is_ascending: 1 (means sort sort_column2 in ascending order)

In formula # 2, the sort_column (the logical part) is based on the “truck numbers” and the sort_column2 is also the second column that contains the “truck numbers).

I hope you could understand it.

Sort by Number of Occurrences Using the Sort Menu in Google Sheets

Here once again I am going to use the same sample data in the array A2:C.

Here, for the example, I am going to use the Sort menu to sort the items by their number of occurrences in Google Sheets.

We are going to use a helper range D2:D. So in cell D2 insert the following formula.

=ArrayFormula(IF(len(A2:A),countif(A2:A,A2:A),))

Then select A2:D11 and click Data > Sort range and follow the below settings (please refer to the image below).

That’s all about how to sort by number of occurrences using the Sort menu in Google Sheets.

Thanks for the stay. Enjoy!