=sum not working

[deanl33069]

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

 

[shg]

MID returns text.

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

 

[Fluff]

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

 

[steve the fish]

Presuming the formula producing the numbers as you expect try:

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

 

[deanl33069]

tyvm works great.
what does the -- do?

 

[Fluff]

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),"")

 

[deanl33069]

Thats what i THOUGHT ..AGAIN tyvm

 

[steve the fish]

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.

 

[Fluff]

Glad we could help & thanks for the feedback