'SUMIFS' function does not distinguish between text 021 and 21
Results 1 to 6 of 6

Thread: 'SUMIFS' function does not distinguish between text 021 and 21

  1. #1
    Join Date
    Jul 2002
    Location
    England
    Posts
    136

    'SUMIFS' function does not distinguish between text 021 and 21

    I'm using the following 'SUMIFS' where column H is formatted as text and contains 021 and 21. However the function is unable to distinguish between the two.

    =SUMIFS('Trial Balances'!$G:$G,'Trial Balances'!$H:$H,'Consol by company'!E$1,'Trial Balances'!$J:$J,'Consol by company'!$A21)

    I'd be grateful if someone could come up with a solution to this.

    Many thanks

    Wilf

  2. #2
    Join Date
    Feb 2000
    Location
    Idaho Falls, Idaho, USA
    Posts
    18,047
    So your matching criteria for range1 is the value at:
    'Consol by company'!E$1

    if you were wanting a match to equal that value, you would need to specify that. See the link below:

    https://www.techonthenet.com/excel/formulas/sumifs.php

  3. #3
    Join Date
    Jul 2002
    Location
    England
    Posts
    136
    Thanks for getting back to me.

    I've specified the value but still have the same problem in that the function doesn't distinguish between "021" and "21" even though the columns are formatted as text.

    Wilf

  4. #4
    Join Date
    Feb 2000
    Location
    Idaho Falls, Idaho, USA
    Posts
    18,047
    After some additional research and testing, it appears that once again, Microsoft is assuming it knows what you want to do instead of providing a means for you to tell the function what your data is.

    Several of the built-in Excel functions will assume that a numeric string of characters is a number even if you have it formatted as text. SUMIF and SUMIFS are two of those functions.

    To get the SUMIFS function to work as you desire, you will need to either add a leading (prefix) letter to your Criteria values, or add a trailing (suffix) letter or character.

    https://www.excelbanter.com/excel-wo...ding-000s.html

    https://a4accounting.com.au/sumifs-function-warning/

    Alternatives include using a different function, a pivot table, or a VBA macro to produce the desired results.

  5. #5
    Join Date
    Jul 2002
    Location
    England
    Posts
    136
    Thanks again for your help and the links. Looks as though I'll have to take your advice and prefix the numbers with an alphabet character. Annoying because at the moment the data is extracted directly from the database and I'll have to add this step.

    Regards
    Wilf

  6. #6
    Join Date
    Feb 2000
    Location
    Idaho Falls, Idaho, USA
    Posts
    18,047
    An Excel VBA macro could be used to add a prefix character if you want to use the SUMIFS function, or a macro could be used to replace the SUMIFS functionality.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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