COUNTIF in a list that contains numbers formatted as text

[HMSTPI]

Excel 2013 64-Bit: I have a list of text that will NOT count correctly (to get the alphabetical order) and, for the first time I know of, I am unable to find any previous discussion anywhere that is relevant.

​

[TABLE="width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ITEM[/TD]
[TD]ORDER[/TD]
[TD]FORMULATEXT[/TD]
[TD]CORRECT ORDER[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1062287
[/TD]
[TD]0[/TD]
[TD]=COUNTIF($A$2:$A$6,"<="&$A2)[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1062740 Folio
[/TD]
[TD]2[/TD]
[TD]=COUNTIF($A$2:$A$6,"<="&$A3)[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1063209 Part
[/TD]
[TD]3[/TD]
[TD]=COUNTIF($A$2:$A$6,"<="&$A4)[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1063648
[/TD]
[TD]0[/TD]
[TD]=COUNTIF($A$2:$A$6,"<="&$A5)[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1063832 Folio[/TD]
[TD]5[/TD]
[TD]=COUNTIF($A$2:$A$6,"<="&$A6)[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
​

Column A and the values within are formatted as TEXT.. When that alone didn't work, I added ' to the beginning of the text in A2 and A5. The ORDER will NOT calculate correctly no matter what I try because it insists on treating A2 and A5 as values. Any suggestions other than adding space or concatenating it with another text value (which I already confirmed does work but SHOULDN'T BE NECESSARY)?

Thanks!

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(--($A$2:$A$6&""<=A2&""))

instead.

 

[mart37]

=sumproduct(($a$2:$a$6<=$a2)*1)

 

[HMSTPI]

Neither option seem to give the right answer. I ended up having to create a different field to concatenate some text in front of the real sorting target to get it to work. All the years I've worked with Excel -- the text/number/number/text inconsistencies and/or auto-treatment STILL finds a new way to drive me crazy!!!

THANKS for such a quick response though I had already moved on and took this long to reply!!