How to Rank Rows by Two Columns in Excel 2019
Question details
The user needs to create sequential ranks or occurrence numbers based on the combined values of two different columns in Excel.

- 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.
Ensure your dataset contains no blank rows and decide which adjacent column will be used as a helper column for the combined values.
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.
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.
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.
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.

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. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data you want to rank.
- 2. Insert the helper formula: Type =A3&B3 in your designated helper column to combine the two data points into a single string.
- 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.

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.




