How to Fix Excel RANK and COUNTIF Formula Not Returning Correct Rank with Multiple Duplicates

When working within Microsoft 365 and Office | Excel | For business | Windows environments, users often rely on the =RANK()+COUNTIF()-1 logic to create unique sequential ranks and break ties. However, you may find the RANK function & Countif not returning correct rank when over 2 duplicates , especially with percentage values. This happens because Excel calculates percentages (like division results) using floating-point

Understanding Why RANK function & Countif not returning correct rank when over 2 duplicates Happens

When working within Microsoft 365 and Office | Excel | For business | Windows environments, users often rely on the =RANK()+COUNTIF()-1 logic to create unique sequential ranks and break ties. However, you may find the RANK function & Countif not returning correct rank when over 2 duplicates , especially with percentage values. This happens because Excel calculates percentages (like division results) using floating-point arithmetic. Two cells might display as "8.50%", but underlying values might be 0.0850000000000001 and 0.0850000000000000

Because the underlying decimals do not match exactly, the RANK function might group them differently, or the COUNTIF function fails to recognize the third instance as a duplicate of the first. To fix this, you must force Excel to evaluate the exact rounded value of the percentage before ranking it, ensuring that 3, 4, or 5 identical percentages are treated as true duplicates

to Fix RANK function & Countif not returning correct rank when over 2 duplicates

Work through the following Excel RANK and COUNTIF Formula Not Returning Correct Rank with Multiple Duplicates sequence in Excel, beginning from Formula Bar.

1. Open your spreadsheet and click on the first cell in your ranking column (e.g., cell C5)
2. Click into the Formula Bar at the top of the Excel window
3. Replace your existing formula with the exact precision-adjusted formula: =IF(B5="","",(RANK(ROUND(B5,4),ROUND(B$5:B$32,4),0))+COUNTIF(B$5:B5,ROUND(B5,4))-1) . Note: If your Excel version does not support array operations on the RANK range, use an auxiliary helper column to round column B first
4. (Alternative without array for older versions) Insert a new column next to your percentages. Enter =ROUND(B5, 4) in the new cell
5. Apply the standard tie-breaker formula to the newly rounded column: =IF(C5="","",(RANK.EQ(C5,C$5:C$32,0))+COUNTIF(C$5:C5,C5)-1)
6. Press Enter to apply the formula
7. Hover over the bottom-right corner of the cell until the cursor becomes a crosshair, then double-click or drag the fill handle down to row 32 to apply the corrected formula. Verify that all 3+ duplicates now display sequential ranks (e.g., 8, 9, 10)

How to Verify the Excel Result

The workflow is complete only after this confirmation: Hover over the bottom-right corner of the cell until the cursor becomes a crosshair, then double-click or drag the fill handle down to row 32 to apply the corrected formula. Verify that all 3+ duplicates now display sequential ranks (e.g., 8, 9, 10) If the expected state is missing, revisit Replace your existing formula with the exact in Formula Bar.

Complete This Local Workflow with WPS Spreadsheets

Use Word, Excel, and PPT for FREE

Free Download

For Excel RANK and COUNTIF Formula Not Returning Correct Rank with Multiple Duplicates, WPS Office provides a free, lightweight route for this local Excel task. WPS Spreadsheets supports common Microsoft Office files in a familiar interface and adds PDF tools and WPS AI for drafting, summarizing, formulas, and routine document work.

1. Open the worksheet in WPS Spreadsheets and select the first result cell.
2. Enter the RANK.EQ formula for the score range and add COUNTIF for a deterministic duplicate order.
3. Lock the comparison range with absolute references before filling the formula down.
4. Check several duplicate scores and confirm that each row receives the intended rank.

Excel FAQs About Excel RANK and COUNTIF Formula Not Returning Correct Rank with Multiple Duplicates

Why does my COUNTIF formula only work for the first two duplicates?

When dealing with calculated percentages, Excel stores numbers up to 15 decimal places. The first two percentages might coincidentally match up to the 15th decimal, but the third calculation might differ by a fraction (e.g., 0.050000000000001). COUNTIF sees this as a different number. Using the ROUND function standardizes all data points.

What is the difference between RANK and RANK.EQ?

RANK is a deprecated legacy function in modern spreadsheet software. RANK.EQ replaces it and performs the exact same operation (giving identical numbers the same top rank). It is best practice to use RANK.EQ moving forward to ensure long-term compatibility with your spreadsheets.

Why does my rank formula return a VALUE! error when I add COUNTIF?

This usually happens if your IF statement leaves the cell blank using "" and a subsequent calculation tries to perform math on that blank text string. Ensure your formula evaluates the IF statement first: =IF(B5="","", [Formula]) so that blank cells are ignored entirely by the mathematical functions.

Can I rank data across multiple non-contiguous columns?

Yes, but you cannot use a simple continuous range like B$5:B$32. You must define a named range that includes all your non-contiguous cells, or use the syntax RANK.EQ(B5, (B5:B10, D5:D10, F5:F10), 0) . However, applying a running COUNTIF tie-breaker across non-contiguous ranges is highly complex and usually requires VBA or specialized helper columns.