# Sum numbers only

#### BORUCH

##### Well-known Member
HI ALL

IN CELL A1 I HAVE 1
IN CELL A2 I HAVE H2
IN CELL A3 I HAVE D2
IN CELL A4 I HAVE 2

I would like to sum the entire range but only the numbers not the letters

THE SUM SHOULD BE 7

p.s. there is not space between the h or d to the number

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Are the below a possibility?

H11
HH1
D132

Are the below a possibility?

H11
HH1
D132

Yes It CAN I’m Sorry For Not Including It, It Could Also Be 2000 H2000 D3000

Is the HH2 included in being a possibility, i.e. more than 1 letter?

Is the HH2 included in being a possibility, i.e. more than 1 letter?

no that’s not a possibility

Is the HH2 included in being a possibility, i.e. more than 1 letter?
no that’s not a possibility
In that case, give this formula a try...

=SUMPRODUCT(0+MID(A1:A4,2-ISNUMBER(0+LEFT(A1:A4)),15))

1% OR 200% is also possible I want it to only sum the number.
for us the % sign means something else

1% OR 200% is also possible I want it to only sum the number.
for us the % sign means something else
You have to give us all the controls (preferably at the same time) if you expect to get a usable answer. Here is the formula I posted in Message #6 modified to ignore percent signs...

=SUMPRODUCT(0+SUBSTITUTE(MID(A1:A4,2-ISNUMBER(0+LEFT(A1:A4)),15),"%",""))

In B2 enter and copy down:

=IF(A1="",0,REPLACE(A1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")-1),"")+0)

and SUM the range in column B.

You have to give us all the controls (preferably at the same time) if you expect to get a usable answer. Here is the formula I posted in Message #6 modified to ignore percent signs...

=SUMPRODUCT(0+SUBSTITUTE(MID(A1:A4,2-ISNUMBER(0+LEFT(A1:A4)),15),"%",""))

THE PERCENTAGE SIGN IS NOT WORKING
 H3 H2 H1 H1 H3 H1 H1 H1 H2 H3 H4 H1 H1 1% 2% 2% 2% 1% 1% 1% 1% 1%

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

I'M GETTING A SUM OF 24.12

Replies
3
Views
110
Replies
4
Views
181
Replies
2
Views
43
Replies
20
Views
673
Replies
0
Views
87

1,203,071
Messages
6,053,375
Members
444,658
Latest member
lhollingsworth

### 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.

### Which adblocker are you using?

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

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