Well, I guess some values end in a normal space, which is fixed with TRIM. Others end in a non-breaking space (character code 160) which isn't. Others probably have neither. So, we use SUBSTITUTE to sort out the CHAR(160) and TRIM to remove the real trailing space(s). As shown, you can just combine the functions.

As for the COUNTIF, no idea.