# Thread: =sum not working Thanks:  2 Post #5311450 (1)Post #5311449 (1) Likes:  2 Post #5311449 (1)Post #5311450 (1)

1. ## =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. ## Re: =sum not working

MID returns text.

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

3. ## Re: =sum not working

The MID function returns a string, so you need to use
=iferror(--mid(A6,find(320,a6)+4,6),"")

4. ## Re: =sum not working

Presuming the formula producing the numbers as you expect try:

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

5. ## Re: =sum not working

tyvm works great.
what does the -- do?

6. ## 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),"")

7. ## Re: =sum not working

Thats what i THOUGHT ..AGAIN tyvm

8. ## 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. ## Re: =sum not working

Glad we could help & thanks for the feedback