=sum not working

deanl33069

Board Regular
Joined
May 2, 2019
Messages
120
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
MID returns text.

=iferror(--mid(A6,find(320,a6) + 4, 6), "")
 
Upvote 0
The MID function returns a string, so you need to use
=iferror(--mid(A6,find(320,a6)+4,6),"")
 
Upvote 0
Presuming the formula producing the numbers as you expect try:

=IFERROR(0+MID(A6,FIND(320,A6)+4,6),"")
 
Upvote 0
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),"")
 
Upvote 0
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.
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top