Sum

[emvella]

Hi,

How can I paste numbers with dash in between like the ones below and it calculates the total 37? In other words I want a formula that it removes the dash and calculate all the numbers together 2+2+4+2+2 etc....

2-2
4-2
2-0
1-2
1-1
2-3
6-0
3-1
3-2
___
37

Thanks

 

[sandy666]

Text2Columns
then:

[Table="width:, class:head"][tr=bgcolor:#000000][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

1​

[/td][td]2-2[/td][td]

2​

[/td][td]

2​

[/td][td]

4​

[/td][td]=SUM(B1:C1)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

2​

[/td][td]4-2[/td][td]

4​

[/td][td]

2​

[/td][td]

6​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

3​

[/td][td]2-0[/td][td]

2​

[/td][td]

0​

[/td][td]

2​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

4​

[/td][td]1-2[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

5​

[/td][td]1-1[/td][td]

1​

[/td][td]

1​

[/td][td]

2​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

6​

[/td][td]2-3[/td][td]

2​

[/td][td]

3​

[/td][td]

5​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

7​

[/td][td]6-0[/td][td]

6​

[/td][td]

0​

[/td][td]

6​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

8​

[/td][td]3-1[/td][td]

3​

[/td][td]

1​

[/td][td]

4​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

9​

[/td][td]3-2[/td][td]

3​

[/td][td]

2​

[/td][td]

5​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

10​

[/td][td][/td][td][/td][td][/td][td=bgcolor:#FFFF00]

37​

[/td][td]=SUM(D1:D9)[/td][/tr]
[/table]

 

[JustynaMK]

Hi,

If you have your range in cells A1:A9 and they are formatted as text, you can use the following:
=SUM(VALUE(LEFT(A1:A9,1)),VALUE(RIGHT(A1:A9,1)))
Enter it using Ctrl+Shift+Enter

Hope it helps!

 

[Peter_SSs]

.. or with standard entry
=SUMPRODUCT(LEFT(A1:A9,FIND("-",A1:A9)-1)+REPLACE(A1:A9,1,FIND("-",A1:A9),""))

If we knew they were always single digits then it reduces to
=SUMPRODUCT(LEFT(A1:A9)+RIGHT(A1:A9))

 

[emvella]

Hi Guys,

That's perfect.

Thank you all.

 

[Peter_SSs]

Hi Guys,

That's perfect.

Thank you all.

Glad you got a successful outcome.