#### jubszz

##### New Member
Rite guys
I have the following numbers on my sheet. They are amounts in money and people initials. how do I just add up the numbers. if they were just numbers I wwould have just used A1:A6
 825.90mh 352.70kf 650.22hj 952.25nk 354.02nm 758.25fd Any help

<colgroup><col style="mso-width-source:userset;mso-width-alt:5449;width:112pt" width="149"> </colgroup><tbody>
</tbody>

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Philosophaie

##### Active Member
How do I just add up the numbers in A1:A6 :
 825.90mh 352.70kf 650.22hj 952.25nk 354.02nm 758.25fd Any help

<tbody>
</tbody>

n(1)=Left(Range("A1"),len(Range("A1"))-2)
n(2)=Left(Range("A2"),len(Range("A2"))-2)
...
n(6)=Left(Range("A6"),len(Range("A6"))-2)

num=n(1)+n(2)+n(3)+...+n(6)

#### miss_ell

##### Active Member
Hello,

You can use this formula in column B and copy down, then sum column B:
=LEFT(A2,LEN(A2)-2)

Or sum column A with this formula (which would be quite awkward with a lot of data):
=LEFT(A1,LEN(A1)-2)+LEFT(A2,LEN(A2)-2)+LEFT(A3,LEN(A3)-2)+LEFT(A4,LEN(A4)-2)+LEFT(A5,LEN(A5)-2)+LEFT(A6,LEN(A6)-2)

I prefer the first option myself.

#### nuked

##### Well-known Member
You could write your own worksheet function:-

(1) Alt+F11
(2) Insert => Module
(3) Paste the following code

Code:
``````Option Explicit
Function SumNumbers(ByRef Target As Range) As Double
Dim i As Integer
Dim strTmp As String
Dim dTmp As Double
Dim rng As Range

For Each rng In Target.Cells

For i = 1 To Len(rng)

If IsNumeric(Mid(rng, i, 1)) Then strTmp = strTmp & Mid(rng, i, 1)

Next i

dTmp = dTmp + CDbl(strTmp)
strTmp = ""

Next rng

SumNumbers = dTmp

End Function``````

You can then use it on your worksheet as you would any other function:

=SumNumbers(A1:A100)

..for example

#### Peter_SSs

##### MrExcel MVP, Moderator

They are amounts in money and people initials.
Assuming the initials are always 2 characters ..

Excel Workbook
B
1
2825.90mh
3352.70kf
4650.22hj
5952.25nk
6354.02nm
7758.25fd
8
93893.34
SUM

#### nuked

##### Well-known Member
Or this one will handle fractions too:

Code:
``````Option Explicit

Function SumNumbers(ByRef Target As Range) As Double
Dim i As Integer
Dim strTmp As String
Dim dTmp As Double
Dim rng As Range

For Each rng In Target.Cells

For i = 1 To Len(rng)

If IsNumeric(Mid(rng, i, 1)) Or Asc(Mid(rng, i, 1)) = 46 Then strTmp = strTmp & Mid(rng, i, 1)

Next i

dTmp = dTmp + CDbl(strTmp)
strTmp = ""

Next rng

SumNumbers = dTmp

End Function``````

#### jubszz

##### New Member

Assuming the initials are always 2 characters ..

SUM

 * B 1 * 2 825.90mh 3 352.70kf 4 650.22hj 5 952.25nk 6 354.02nm 7 758.25fd 8 * 9 3893.34

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:77px;"></colgroup><tbody>
</tbody>

 Cell Formula B9 =SUMPRODUCT(--(LEFT(B2:B7,LEN(B2:B7)-2)))

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

this just tells me #VALUE!

#### miss_ell

##### Active Member
Assuming the initials are always 2 characters ..
(snip)

Peter,

That's the best solution! I forgot about SUMPRODUCT, then again I don't use it that much...

#### Peter_SSs

##### MrExcel MVP, Moderator
this just tells me #VALUE!
Does it not work for the sample data provided?
Did you take note of my assumption?
Does it mean that your original sample wasn't representative?
Assuming the initials are always 2 characters ..

Or this one will handle fractions too:
Here's a simpler UDF that should do that too. No need to loop through each character in the string. This should handle negative numbers too and it doesn't error if there are any blank cells in the range.
Code:
``````Function SumExcl(r As Range) As Double
Dim Tot As Double
Dim c As Range

For Each c In r
Tot = Tot + Val(c.Value)
Next c
SumExcl = Tot
End Function``````

#### jubszz

##### New Member
Peter,

That's the best solution! I forgot about SUMPRODUCT, then again I don't use it that much...

Peter
that works fine
i wasnt looking at the cell ranges i was using, after playing around with it. It now works fine

thank you Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,008
Messages
5,834,853
Members
430,324
Latest member
bosphoruskid ### 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