Sum numbers only

[BORUCH]

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

 

[MARK858]

Are the below a possibility?


H11
HH1
D132

 

[BORUCH]

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

 

[MARK858]

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

 

[BORUCH]

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

no that’s not a possibility

 

[Rick Rothstein]

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

 

[BORUCH]

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

 

[Rick Rothstein]

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

 

[Aladin Akyurek]

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.

 

[BORUCH]

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

HI thanks for your time

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