Results 1 to 9 of 9

Thread: =sum not working

  1. #1
    Board Regular
    Join Date
    May 2019
    Posts
    86
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default =sum not working

    I have a sheet with a formula to grab numbers from a string from this formula =iferror(mid(A6,find(320,a6)+4,6),"") in the H column. i have the formula =sum(H:H) in the N column and always get 0 for a response instead of the total.
    I have this same formula on other sheets and it works fine. Must be doing something dumb....

    ty

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,612
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: =sum not working

    MID returns text.

    =iferror(--mid(A6,find(320,a6) + 4, 6), "")

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,205
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: =sum not working

    The MID function returns a string, so you need to use
    =iferror(--mid(A6,find(320,a6)+4,6),"")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,649
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: =sum not working

    Presuming the formula producing the numbers as you expect try:

    =IFERROR(0+MID(A6,FIND(320,A6)+4,6),"")

  5. #5
    Board Regular
    Join Date
    May 2019
    Posts
    86
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: =sum not working

    tyvm works great.
    what does the -- do?

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,205
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: =sum not working

    It converts the string to a number.
    You can also do it like Steve did in post#4, or like
    =IFERROR(1*MID(A6,FIND(320,A6)+4,6),"")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    May 2019
    Posts
    86
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: =sum not working

    Thats what i THOUGHT ..AGAIN tyvm

  8. #8
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,649
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: =sum not working

    If you type a minus sign in front of a textual number (MID always produces text even if that text looks like a number) excel will coerce that text into a number if possible or produce an error. Lets says your formula produces 10. You will now have -10. The second minus sign is to reverse the sign back. You now have a real number, in this case 10, that excel can sum.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,205
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: =sum not working

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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