How to Rank Text Uniquely in Google Sheets

Published on

Using a combination of two conditional count functions, we can rank text values uniquely in Google Sheets. They are Countif and Countifs.

Earlier I have shared a formula to rank data alphabetically. We can’t use the same here as that will give the same rank to duplicate texts.

For example, if “apple” repeats twice and the rank of apple is 1 in the list, both will get the rank 1.

When we rank text values uniquely without duplicates, the first “apple” will get rank # 1, and the second “apple” will get rank # 2. It will affect the ranking of other text values in the list.

Before going to the formula part, one more thing.

You may have a list that is sorted or unsorted. My formula will work smoothly in both scenarios.

Please pay attention to column C in the following two tables.

Unsorted (Randomized) List:

Rank Text Uniquely in an Unsorted List

Sorted List:

Rank Text Uniquely in a Sorted List

Unique Rank of Texts

Generic Formula:

Unique Rank of Text = Count_of_Text_Below + Running_Count_of_Text

Arguments:

Count_of_Text_Below – The number of texts < (less than) the text in question. To understand it, let’s consider the above-sorted list.

The cells B2, B3, and B4 contain “apple.” There is no text less than it. So the count of text below will be 0 (zero) each.

What about the count of “grape” in cells B5 and B6. It will be three each as there are three items (apples) less than it below.

Running_Count_of_Text – In the sorted list, the running count of “apple” in cells B2, B3, and B4 will be 1, 2, and 3, respectively.

What about the running count of “grape” in cells B5 and B6. It will be 1 and 2, respectively.

Based on the above, we can rank texts uniquely as below in Google Sheets.

Item Count_of_Text_Below Running_Count_of_Text Unique Rank of Text
apple011
apple022
apple033
grape314
grape325

Note:- I have only picked part of the items from the sorted list for the explanation.

Formulas to Rank Text Values Uniquely in Google Sheets

We will start with a drag-down formula.

Here is the formula to return the rank of text values uniquely in Google Sheets. Insert it in cell C2 and copy-paste it down as far as needed.

=COUNTIF($B$2:$B,"<"&B2)+COUNTIF($B$2:B2,B2)

Count_of_Text_Below = COUNTIF($B$2:$B,"<"&B2)

Running_Count_of_Text = COUNTIF($B$2:B2,B2)

It will work correctly even if the list is unsorted.

Can we use an array formula here?

Yes. Here we can use two count functions. They are COUNTIF and COUNTIFS.

You May Like: How to Use All Google Sheets Count Functions [All 8 Count Functions]

First of all, empty C2:C. Then insert the following array formula in cell C2 to rank texts uniquely.

=ArrayFormula(
     if(B2:B="",,
        COUNTIF(B2:B,"<"&B2:B)+
        COUNTIFS(B2:B,B2:B,row(B2:B),"<="&ROW(B2:B))
     )
)

Count_of_Text_Below – COUNTIF(B2:B,"<"&B2:B)

Running_Count_of_Text – COUNTIFS(B2:B,B2:B,row(B2:B),"<="&ROW(B2:B))

The formula if(B2:B="",, is to limit the output in non-blank rows.

That’s all. This way, you can rank text uniquely without duplicates in Google Sheets.

Thanks for the stay. Enjoy!

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.