Results 1 to 8 of 8
Thread: Countif anomaly  Excel 2003

20090721, 22:23 #1
 Join Date
 Jun 2009
 Location
 Brisbane, Australia
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20090721, 23:17 #2
 Join Date
 Jan 2001
 Location
 Perth, Western Australia, Australia
 Posts
 190
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='wyldchild' post='785687' date='22Jul2009 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

20090722, 03:44 #3
 Join Date
 Jun 2009
 Location
 Brisbane, Australia
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='kieran' post='785689' date='22Jul2009 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.
Any other suggestions gratefully received.
Cheers
Wyld Child

20090722, 03:53 #4
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,272
 Thanks
 3
 Thanked 187 Times in 173 Posts
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.
Regards,
Rory
Microsoft MVP  Excel

20090722, 04:16 #5
 Join Date
 Jun 2009
 Location
 Brisbane, Australia
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20090722, 05:01 #6
 Join Date
 Jun 2009
 Location
 India
 Posts
 17
 Thanks
 0
 Thanked 0 Times in 0 Posts
could you paste the formula and the name from DATA set, to have a look, what your trying.

20090722, 05:05 #7
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,272
 Thanks
 3
 Thanked 187 Times in 173 Posts
Does the data you are using contain any special characters like ? * or ~ by any chance?
Regards,
Rory
Microsoft MVP  Excel

20090722, 16:01 #8
 Join Date
 Jun 2009
 Location
 Brisbane, Australia
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
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