CODEWITHSUNDEEP
excelsortingsequence
Koen073
Koen073

Reputation: 3

Sorting issue NL vs. DE machine

I have an Excel sheet with in 'column A' a technical code (type: text). In 'column B' I have a formula that counts how many cells in 'Column A' are smaller or equal to them, so I have the relative position and can sort them later on. The formula in Cell B2 =COUNTIF($A$2:$A$443;"<="&A2)

Screenshot of the Excel list I have Within my Excel this works fine, as I got the postion for each cell in 'Column B'. But for my German colleagues this doesn't work for some reason. See screenshot below. ID 4062.2.1 does not get position 6 but for some reason a 0.

Error in German Excel So my question is: Does someone know why this sorting doesn't work on all machines? And do you have a solution, maybe another formula that I could try?

Upvotes: 0

Views: 30

Answers (2)

Koen073
Koen073

Reputation: 3

I changed the dot (.) in the ID to an underscore (_) and now it worked also on German settings Excel.

Vielen dank!

Upvotes: 0

Axel Richter
Axel Richter

Reputation: 61852

The problem is if A2 contains 4062.2.1 then this is a date format in Germany. It is February, 1. 4062. So =COUNTIF($A$2:$A$443;"<="&A2) leads to <=4062.2.1 as the criterion because of the concatenation "<="&A2. And this is the same as <=789688, which is the date value of 4062-2-1.

As far as I know there is no way around except not using that kind of ID formats which can be in conflict with numbers or dates if you wants using COUNTIF which needs concatenating the criterion.

In your special case you could try

=SUMPRODUCT(($A$2:$A$443<>"")*($A$2:$A$443<=A2))

This does not need any concatenation.

Upvotes: 1

Related Questions