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)…
Related Posts

Coefficient Of Variation in Excel/Google Sheet

This article will talk about how to calculate the coefficient of variation by formula in a Microsoft Excel spreadsheet or Google Sheets.  What is the Coefficient of Variation? The coefficient of variation is the ratio of the standard deviation of ...

8+ Best Free Monthly Budget Template

This article will show you some free and printable personal monthly budget templates or family monthly budgets for google sheet and Microsoft Excel spreadsheet applications, and will also explain some of the features or important functions of these templates. This ...

Best Free Wedding Planning Checklist

Everyone will be very much looking forward to their wedding, but actually preparing a wedding is very complicated and trivial, there are many small details to pay attention to, we will list in this article a comprehensive list of each ...

Free House Cleaning Schedule and Checklist (Daily, Weekly, Monthly)

This article will show you some free daily house cleaning schedule, weekly house cleaning schedule and monthly house cleaning schedule templates for google sheet and Microsoft Excel spreadsheet applications and will also explain some of the features or important functions ...

Find the Closest Data to the Data Provided in Google Sheets

In our daily work, we may encounter such an issue that to find the closest value to a certain value. In fact, google sheets internal functions can help us solve this problem. In today’s article, we will show you how ...

Sidebar