Countifs assigns same value to a cell when the value is different if there are mixed number and text cells in column

[SimonTon]

=IF($I6="","",IF($I6="Retired","",IF($I6="Current",COUNTIFS(PStatus,"Current",PVRN,"<="&$S6)+1,"")-1))

I have been using this formula to rank vehicle registrations alphabetically and this was working fine. I then used the same formula to rank plant items alphabetically. The plant items however either have a reg or a number rather than just a reg. The above formula will assign a text value the same number as one with a numerical value. Can anyone tell me why?

The below is not real data but an example of what is returned on the spreadsheet with the formula being in column B

A B
Identifier Rank
020 3
024 4
015 2
Test 2
003 1
Example 1

Why does the formula not rank the text after or before the numerical value?

I hope this makes sense and someone can help?

Cheers,

Simon

 

[sijpie]

Hi Simon,

your example data are in column A & B. Yet your formula acts on column I and S and named ranges PStatus and PVRN.

So difficult to explain for me what the formula does without knowing what's what