# =sum not working

#### deanl33069

##### Board Regular
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

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### shg

##### MrExcel MVP
MID returns text.

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

#### Fluff

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

#### steve the fish

##### Well-known Member
Presuming the formula producing the numbers as you expect try:

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

#### deanl33069

##### Board Regular
tyvm works great.
what does the -- do?

#### Fluff

##### MrExcel MVP, Moderator
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

##### Board Regular
Thats what i THOUGHT ..AGAIN tyvm

#### steve the fish

##### Well-known Member
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

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback

1,101,943
Messages
5,483,823
Members
407,415
Latest member
Anton1999

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...