Rand Data by the Number of Occurrences in Google sheets

This article will talk about how to sort the specified data in google sheets according to the number of occurrences? How to use google sheets RANK function to sort a given cell range by the number of repetitions of a value?

Rand Data by the Number of Occurrences in Google sheets6

Rank Data by the Number of Occurrences

Suppose you have a task that needs to be ranked according to the number of occurrences. How to sort the data in a given cell range by the number of repetitions in google sheets.

First you need to count the number of occurrences of the values in the given data range, and then use the RANK function to sort the data. You can follow the steps below.

Step 1: You first need to copy the list of data and paste it into another data column as follows.

Rand Data by the Number of Occurrences in Google sheets1

Step 2: Select “Data” Tab and click on the “Data CleanUP” menu, then clicking “remove duplicates” sub menu. The “Remove Duplicates” window will pop up.

Rand Data by the Number of Occurrences in Google sheets2

Step 3: Click the “Remove duplicates” button and then click the “ok” button, which will remove all duplicate values and keep only one of each value.

Rand Data by the Number of Occurrences in Google sheets3

Rand Data by the Number of Occurrences in Google sheets4

Step 4: You can use the following COUNTIF formula to get the number of duplicates of the values in the cell area A1:A8, then press the Enter key and drag the AutoFill Handler until cell D5, so you can easily apply the formula.

=COUNTIF($A$1:$A$8,C2)

Rand Data by the Number of Occurrences in Google sheets5

Step 5: You need to use the google sheets RANK function in cell E2 to sort the number of repetitions of the values counted in the previous step, after that press the Enter key and drag the Auto Handler until cell E5.

=RANK(D2,$D$2:$D$5)

Rand Data by the Number of Occurrences in Google sheets6

Now you can see the result of ranking by the number of repetitions of the value has been calculated.

Related Functions


  • Google Sheets RANK Function
    The Google Sheets Rank function is used to return the ranking of a given number from a list. If there are duplicate numbers in the list, then the numbers will be in the same rank.The syntax of the Google Sheets Rank function is explained as follows:The syntax of the Google Sheets Rank function is explained as follows:= RANK (number,reference ,[order])…
  • Google Sheets COUNTIF function
    The google sheets COUNTIF function is used to count the number of cells in a cell range that meet a given condition.The syntax of the Google Sheets COUNTIF function is explained as follows: = COUNTIF (range, criteria)…

Sidebar