Rank Data with Multiple Criteria in google sheets

If you are an enthusiastic Google Sheets user, then you may have an urgent task to rank a specified datelist based on given multiple criteria; you can easily perform this task manually, which is also feasible, but it only works for ranking a small amount of data. However, when there are multiple pieces of data to be processed, it becomes a cumbersome and time-consuming task to sort the list of data based on multiple conditions by manually.

But don’t worry, because this article will show you how to rank with multiple criteria in google sheets.

RANK Data with Multiple Criteria

If you want to rank the specified data by just one criteria, you can use the RANK function to easily rank the values of the range of cells in google sheets. Suppose you want to rank the data of a range of cells in a worksheet with multiple criteria. So how do you accomplish that task? You need to create a new complex formula by using the SUMPRODUCT function. For example, when you want to sort the list of data in the range of cells A1:B4 by using two criteria or conditions, simply use the following formula.

=1+SUMPRODUCT((\$A\$1:\$A\$4=A1)*(\$B\$1:\$B\$4>B1))

Next, you need to type this formula in the formula box in cell C1, then press Enter and drag the AutoFill handler to the other cells.

Let’s see the results.

Related Functions

The Google Sheets SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
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])…

Related Posts

Conditional Formatting based on Another Cell in Google Sheets/Excel

In Microsoft Excel Spreadsheet or google sheets, when you want to format a specified cell or cell range based on the value of a different cell, for example, formatting the first row based on the value of a cell in ...

Compare Two Strings in Excel/Google Sheets

This article will talk about how to compare two given strings in a Microsoft Excel spreadsheet or Google Sheets. How to compare two strings in Excel by using VBA macros to see if they are the same, if they are ...

Compare effect of (non-annual) compounding periods on growth

This article will talk about how to calculate the trend or effect of future value changes for different compounding periods in a Microsoft Excel spreadsheet or Google Sheets via a formula. What is the period of compound interest? The compounding ...

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

Phone Number Format in Excel

Sometimes when we import phone numbers from external sources, the formatting of the phone numbers is removed. Even if in some situations the phone numbers are not imported from external resource, sometimes the phone numbers in worksheet are not displayed ...

Sidebar