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

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 ...

Basic Array Formula With Examples in Google Sheets

Are you searching for an article for getting the basic array formula with different examples for better understanding? Then congratulations because you have just landed on the right article. In this article, you would get to know the basic array ...

Google Sheets WORKDAY Function
google sheets workday function1

This post will guide you how to use Google Sheets WORKDAY function with syntax and examples. Description The Google Sheets WORKDAY function returns a date or a serial number that represents a date that is the indicated number of working ...

How to Use COUNTIFS function with Multiple Criteria and OR Logic in Excel
countifs function with multiple criteria or logic4

This post will guide you how to Use COUNTIFS function with multiple criteria and OR logic in Excel 2013/2016 or Excel office 365. How to count with multiple criteria and OR logic in Excel. COUNTIFS with Multiple Criteria and OR ...

How to Count Numbers that begin with Specific Value  in Excel

This post will guide you how to count numbers that begin with a specific value or number in Excel 2013/2016 or Excel office 365. How do I count the number of values that begin with a given specific value by ...

How to Sum Data if by Year in Excel

In statistic, to create a yearly report for showing the market trend on different years, we often sum a total for different years, so based on the year’s total we can do estimation for the coming year. This scenario frequently ...

How to Convert Fahrenheit to Celsius or Kelvin in Microsoft Excel
convert fahrenheit to celsius3

This post will guide you how to Convert Temperatures among Fahrenheit, Celsius, and kelvin using a formula in Microsoft Excel. How do I convert Fahrenheit to Celsius in Excel 2013/2016. How to convert degrees Fahrenheit to degrees Celsius in Excel. ...

How to Change the Orientation of Worksheet in Excel
change orientation of worksheet1

This post will guide you how to change the default Portrait orientation to Landscape page orientation in your worksheet in Excel. Change the Orientation of Worksheet When you have a list of data with lots of columns, you can try ...

Limit Formula Result to Maximum or Minimum Value
limit formula result2

This post will guide you how to limit formula result to a maximum or minimum value in excel. For example, if you want to limit the results of a formula to a maximum value of 200, how to this this ...

Sidebar