Results 1 to 8 of 8
  1. #1
    New Lounger
    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

  2. #2
    2 Star Lounger
    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='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. #3
    New Lounger
    Join Date
    Jun 2009
    Location
    Brisbane, Australia
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post

    [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.

    Any other suggestions gratefully received.

    Cheers

    Wyld Child

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  5. #5
    New Lounger
    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

  6. #6
    New Lounger
    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.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Does the data you are using contain any special characters like ? * or ~ by any chance?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    New Lounger
    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

Posting Permissions

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