Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    Glasgow, Lanarkshire, Scotland
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sumiff snag (Excel 2002 (10.2614.2625))

    Hi,

    Got a snaglet with sumif (see simplified attachment - zipped to fit in!)

    What I'm trying to do is find out how many of each unique code have been scanned.

    There are 2 sheets - 'scanned' and 'available'. In cell 'available'!B2 - the answer there is 4 - which is wrong. There is only one occurrence of item code 10000018514069000302.

    But - if I change this item code on both sheets to lets say 111 - then the value in 'available'!B2 changes to 1 - which is correct.

    Am I being a real numpty somewhere??

    Many thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumiff snag (Excel 2002 (10.2614.2625))

    The item codes are very large numbers, although they are formatted as text. I fear that Excel is not able to compare such large numbers correctly.
    To force Excel to compare them as strings, I inserted columns with formulas such as ="z"&B1. The leading "z" (it could have been any letter, really) ensures that the result is text. When you apply the SUMIF formulas to these columns, the results are correct. You can hide the extra columns. See attached modified workbook.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sumiff snag (Excel 2002 (10.2614.2625))

    I agree with Hans (he beat me to the answer).
    When excel does the comparison, it is converting the 20 digit "text" into a 20 digit number (but only uses the first 15 digits!) so it says they are the same (which is correct to the first 15 digits)

    Why it is doing this is not clear, but converting to text is the best way to go.

    Steve

Posting Permissions

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