Function Problems

How to Rank Rows by Two Columns in Excel 2019

Ayan MasoodAyan Masood Nov 3, 2026 871 views

Question details

The user needs to create sequential ranks or occurrence numbers based on the combined values of two different columns in Excel.

How to Rank Rows by Two Columns in Excel 2019
Product
Excel 2019
Device & OS
not provided
Scenario
Ranking and organizing data rows where uniqueness or sorting depends on the specific combination of data across two columns.
Observed behavior
Duplicate combinations need to receive sequential numbers within their group, and an overall rank must be applied to the combined values.
Before you start

Ensure your dataset contains no blank rows and decide which adjacent column will be used as a helper column for the combined values.

Solution 1Recommended

Rank Rows Using a Helper Column and Formulas

Combine the values of two columns into a helper column to easily rank or count sequential occurrences using standard Excel functions.

By merging the data from two columns into one, you create a unique identifier for each row combination. This allows standard counting and ranking functions like COUNTIF and RANK.EQ to process the data accurately.

1
Create a Helper Column

Insert a new column next to your data. Assuming your data is in columns A and B, select cell C3 and enter the formula =VALUE(A3&B3) for numbers, or simply =A3&B3 for text, to combine them.

2
Count Sequential Occurrences

To assign sequential numbers to duplicate combinations within their group, enter =COUNTIF($C$3:C3,C3) in cell D3 and drag the fill handle down to apply it to your entire dataset.

3
Assign Overall Rank (Optional)

If you need an overall rank across all combined values, use the RANK.EQ function. Enter =RANK.EQ(C3,$C$3:$C$11,0) in cell E3, adjusting the range $C$3:$C$11 to match the exact size of your data.

Rank Rows Using a Helper Column and Formulas
Formula Ranges: Make sure to use an absolute reference (like $C$3) for the start of your range in the COUNTIF formula so the range expands properly as you copy it down.
Powerful Data Management

Easily Rank and Manage Data with WPS Spreadsheet

WPS Spreadsheet supports all standard Excel formulas, including COUNTIF and RANK.EQ. You can easily rank rows, analyze data, and manage complex datasets using the exact same formulas in a lightweight, user-friendly interface.

  1. 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data you want to rank.
  2. 2. Insert the helper formula: Type =A3&B3 in your designated helper column to combine the two data points into a single string.
  3. 3. Apply the COUNTIF function: Use =COUNTIF($C$3:C3,C3) to establish your sequential rank and drag the fill handle down to apply it to all rows.
Fully compatible with Microsoft Excel (.xlsx) file formats and formulas.Built-in support for advanced functions like COUNTIF, RANK.EQ, and text combination.Lightweight design ensures fast performance even with large datasets.
microsoft office alternative - wps office

Frequently Asked Questions

Can I rank rows by two columns without creating a helper column?

Yes, you can use more complex array formulas like SUMPRODUCT or COUNTIFS, but using a helper column is generally the most reliable and easiest method to understand and troubleshoot.

Why is my COUNTIF formula returning the same number for every row?

This usually happens if you did not lock the starting cell reference. Ensure you use a mix of absolute and relative references, such as $C$3:C3, so the counting range expands dynamically as you drag the formula down.

Does the VALUE function work if my columns contain text?

No, the VALUE function will return a #VALUE! error if it attempts to convert non-numeric text. If your combined columns contain text, simply use =A3&B3 without wrapping it in the VALUE function.