Rank Based on Specific Value in google sheets

This article will guide you how to sort the values in a given column based on a specific value in another column in google sheets. How to sort a range of values in google sheets by using IF function and COUNTIFS function based on a specific value in another column.

Rank Based on Specific Value in google sheets1

Rank Based on Specific Value

Suppose you have a list with two columns of data, where the first column is a list of products and another column is the number of sales. If you have an urgent need to quickly sort the number of sales for a given product name, you can’t solve this request with the RANK function, because it can only be used to sort values up to one range. So is there a quick and easy way for sorting in google sheets? Of course yes, the following will show how to use the IF function in combination with COUNTIFS function to build a new google sheets formula to solve the problem. The formula is as follows.

For example, if you want to rank the sales values in the range B2:B6 based on the specified product name “excel” in the range A2:A6, then you can use the following formula to get the ranking results:

=IF(A2="excel",COUNTIFS(A$2:A$6,"excel",B$2:B$6,">"&B2)+1,"")

Next, you need to type the above formula in another blank cell and press Enter on your keyboard to apply the formula, and after that you need to drag the autofill handler down to the other cells to apply this formula.

Rank Based on Specific Value in google sheets1

As you can see from the example above, the products in column A, “excel“, can now be ranked by their sales.

Let’s See How This Formula Works

=COUNTIFS(A$2:A$6,”excel”)

This formula is used to count the number of excel product names in a given cell range A2:A6.

Rank Based on Specific Value in google sheets1

=COUNTIFS(B$2:B$6,”>”&B2)

This formula is used to count the number of times that the value in the given cell range B2:B6 is greater than the current sales value.

Rank Based on Specific Value in google sheets1

=COUNTIFS(A$2:A$6,”excel”,B$2:B$6,”>”&B2)

The formula is used to count the number of products with sales greater than the current product sales, and must meet one additional condition is that the product name must be excel.

Rank Based on Specific Value in google sheets5

=COUNTIFS(A$2:A$6,”excel”,B$2:B$6,”>”&B2)+1

The returned result of the above formula adding the number 1 is the actual ranking of the specified product in its own product list.

Rank Based on Specific Value in google sheets1

Related Functions

  • Google Sheets IF function
    The Google Sheets IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Google Sheets COUNTIFS function
    The Google SheetsCOUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…
Related Posts

Cap percentage values between 0 and 100

This article will talk about how to cap the percentage values between 0% and 100% in Microsoft Excel Spreadsheet or Google Sheets. If you are a newbie on Excel or google Sheets, you may be able to do this by ...

Calculate Cap Percentages to Specific Value

This article will talk about how to limit the cap percentage of a given amount to a given value in Microsoft Excel Spreadsheet or Google Sheets. If you are a newbie on Excel or google Sheets, you may be able ...

Calculate Compound Interest using FV Function in Google Sheets

This article will show you how to calculate compound interest in Google Sheets. It will help you calculate the interest you will accrue on a given amount of money. There are several ways to calculate compound interest Google Sheets. One ...

Count Attendance and Absence with Google Sheets COUNTIF

In our campus life and work life, we usually record everyone's attendance. Today we will introduce you the application of Google Sheets COUNTIF function to count the attendance. Using a week as an example, we use the following example to ...

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

Average Of Numbers With Multiple Criteria in Google Sheets

Have you ever come across a task to calculate the average of the numbers with respect to multiple criteria in google sheets? Are you tired of doing this cumbersome task manually? Are you willing to do this task smartly in ...

Calculate Average Of Last 5 Or N Values In Columns in Google Sheets
Average last N values in columns in google sheets1

Suppose you come across a task where you need to calculate the average of the last 2 or 3 numeric values in google sheets, then what would you do? If you are new to google sheets, then your first attempt ...

Find And Retrieve Missing Values in Google Sheets
FIND AND RETRIEVE missing values1

Find and retrieve is a common basic operation in Google Sheets for tables. In out daily work, we may encounter this situation that some data was lost after several operations on a table in Google Sheets. In fact, we can ...

Calculating Average Of The Numbers in Google Sheets
_3 Average numbers in google sheets1

Are you weary of investing a lot of time and effort in manually calculating the average of the numbers by including or excluding 0 and calculating the average of the top 3 scores? Then congratulations because you have just landed ...

Sidebar