# Thread: Countif anomaly - Excel 2003

1. Hi,

Hoping for some urgent help with a countif anomoly. I have a spreadsheet where I have used the following formula:

=countif(\$d\$2:\$d\$65000,"John Smith - ABC")

My problem is that there are 62 variations of this same formula (eg: =countif(\$d\$2:\$d\$65000,"Mary Jones - ABC") in column N - 38 instances before it, 23 after it. Every other formula returns the correct value. The example above returns a value of 0.

Through the use of filtering, I know that the value should be 18.

The cell format for column D is text (I changed it to General to see if that would help - it had no effect)

I have manually recreated a sample of the data in a blank workbook, and I can get the formula to work perfectly. The formula also works if I alter the source data and the formula to contain just "John Smith". For legal reasons, the source data needs to remain "John Smith - ABC". What I don't understand is why "Mary Jones - ABC" works, but "John Smith - ABC" does not.

As this is a business sheet, I am unable to upload it for you to see the real thing, but I am hoping someone can tell me where to look to sort out the problem in the live version of the workbook.

Thanking you in anticipation

Wyld Child

2. [quote name='wyldchild' post='785687' date='22-Jul-2009 11:23']Hi,

Hoping for some urgent help with a countif anomoly. I have a spreadsheet where I have used the following formula:

=countif(\$d\$2:\$d\$65000,"John Smith - ABC")

My problem is that there are 62 variations of this same formula (eg: =countif(\$d\$2:\$d\$65000,"Mary Jones - ABC") in column N - 38 instances before it, 23 after it. Every other formula returns the correct value. The example above returns a value of 0.

Through the use of filtering, I know that the value should be 18.

The cell format for column D is text (I changed it to General to see if that would help - it had no effect)

I have manually recreated a sample of the data in a blank workbook, and I can get the formula to work perfectly. The formula also works if I alter the source data and the formula to contain just "John Smith". For legal reasons, the source data needs to remain "John Smith - ABC". What I don't understand is why "Mary Jones - ABC" works, but "John Smith - ABC" does not.

As this is a business sheet, I am unable to upload it for you to see the real thing, but I am hoping someone can tell me where to look to sort out the problem in the live version of the workbook.

Thanking you in anticipation

Wyld Child[/quote]

Could it be that there are additional characters in the data for "John Smith - ABC"?
Eg a) "John Smith - ABC " or
the '-' character is a different character code in the data from that used in the formula.

Check a) by looking at any data cell with John Smith - ABC and check taht there are no leading or trailing spaces.
check by copying & pasting from a data cell the 'John Smith - ABC' string into your formula.

HIH

3. [quote name='kieran' post='785689' date='22-Jul-2009 00:17']Could it be that there are additional characters in the data for "John Smith - ABC"?
Eg a) "John Smith - ABC " or
the '-' character is a different character code in the data from that used in the formula.

Check a) by looking at any data cell with John Smith - ABC and check taht there are no leading or trailing spaces.
check by copying & pasting from a data cell the 'John Smith - ABC' string into your formula.

HIH[/quote]

Hi Kieran,

Thanks for the response. I had already checked both of your first suggestions - in actual fact I had copied the text from the original column (to avoid having typos etc), so that was not the problem. Given that I copied the text from that column in the first place, I would have assumed that the character code for the '-' should have been the same.

Cheers

Wyld Child

4. If you replace the text in your countif with a reference to a cell that contains that text, does the formula calculate properly? If so, then you've got something like a leading or trailing space that you've missed when you copied and pasted.

5. Hi Rory,

Thanks for your suggestion - yes I did try that too - Basically I currently have about 16000 rows of data, so to obtain a unique list of people I used advanced filtering, copying the results to another location and displaying only the unique records. The result was 62 unique records, so I just copied the "john smith - ABC" into formula. When it returned a value of 0, I knew there was a problem, prompting me to check the validity of the results of the other forumlae - no problems with any other formula. I then tried using the a cell reference in the formula, but it did not work either - but only for that one dataset.

Any other suggestions?

Thanks.

Wyld Child

6. could you paste the formula and the name from DATA set, to have a look, what your trying.

7. Does the data you are using contain any special characters like ? * or ~ by any chance?

8. Hi,

No the data did not contain any of those characters, but I have managed to get the formula to work on that particular item of data - though not elegantly, and certainly without working out what was causing the original problem.

What I have eventually resorted to was clearing all formatting from the source worksheet and then copying the whole lot into a whole new workbook and reapplying formatting. In the new workbook, it all works fine.

Just in case you are wondering, I had tried just clearing the formatting in the original sheet and running the formula, but it still persisted in returning a 0 value. I had even tried moving the formula to a different part of the sheet (desperate and not very logical, I know). So, while I am no wiser as to why just one formula out of 62 did not work, I am relieved that I now have a perfectly working version.

Thankyou to all who have taken the time to try and solve my frustrating problem. Very much appreciated.

Regards

Wyldchild

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•