COUNTIFS recognizing 1.1 vs 1.10

[esbon]

I'm trying to properly index ~8,000 data points from a VDR. The VDR indexes files as 1.1, 1.2...1.9, 1.10, 1.11, etc. There are also subfolders so files might be 1.1.1, 1.2.1 etc. I want to sort files into one sublevel deep (i.e., count all the files in 1.1.1, 1.1.2 etc as 1.1). I've been using the below formula to mitigate the issue of needing to pull 4 vs 3 characters. And I'm able to pull info into column C that displays 1.1 vs 1.10 differently.

On a different sheet, I'm collating into the relevant subfolders. For folder 5, I have 5.1 thru 5.47. I am able to use an HLOOKUP in column D of the second screenshot, which pulls 5.1 and 5.10 distinctly. However, the COUNTIFS formula in columns H-J is treating 5.1 the same as 5.10.

Is there a way to solve this issue? Plz help this is driving me crazy

 

[KRice]

I don't know if this would resolve the issues, but have you considered splitting the text into separate columns using the "." as a delimiter? Then you could group, sort, and count based on columns (which would correspond to the original levels/sublevels).

 

[6StringJazzer]

How are you entering the data in column C? I think the way to solve your problem is to format the entire column as Text. In your case I am guessing some values are Text and some are Number. The number 5.10 is the same as the number 5.1 but the Text 5.10 is different than the the text 5.1.

I have this issue all the time with WBS numbers for project plans.