[Mr Pleasant]

Calling Excel experts. Hopefully one of you can make me look stupid.

I have a worksheet full of extracted rows of data from other sheets. A key column is the one containing an identifying code such as "AER", "LAKE" and "AIS". The front worksheet in the workbook is a summary sheet that lists a total for each code, based on the COUNTIF function. It all works perfectly well, except for "LDS". For some reason I simply fail to understand, Excel cannot count the "LDS" codes. The total remains zero. If I manually change the codes to "LDSX" and change the COUNTIF function to count them, bingo, the total appears. When I remove the X, the total returns to zero.

Anyone? Please!

[grahamlthompson]

Have you by any chance got a named range or worksheet called LDS making the string ambigous ?

[Mr Pleasant]

Thanks for the interest, but it appears to be down to 'non-printing' characters or 'whitespace' issues. I've had some success with the CLEAN function, but can't quite integrate it into the macro so far.

[grahamlthompson]

Originally Posted by Mr Pleasant

Thanks for the interest, but it appears to be down to 'non-printing' characters or 'whitespace' issues. I've had some success with the CLEAN function, but can't quite integrate it into the macro so far.

Try the like comparison rather than equality.

For example "sssgNDSjjj" like "*NDS*" is true. If its leading zeros then the right$ function will remove any leading characters