Excel Formula Please?

[sezuh]

Hi
Sorry to bother you again,
I have two query 1;how to sum arraystring of numbers in cells i used “=Sum(A1)” and copy down
It gives me “0” as a result,even i put commas or semicolon between numbers still does not give the right result .cuold someone point me to right direction please.
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Data</TD><TD>Result</TD><TD>expected result</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">1 2 3</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">1 4 7</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">12</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">1 5 9</TD><TD> </TD><TD style="TEXT-ALIGN: right">15</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">1 6 8</TD><TD> </TD><TD style="TEXT-ALIGN: right">15</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">2 4 9</TD><TD> </TD><TD style="TEXT-ALIGN: right">15</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">2 5 8</TD><TD> </TD><TD style="TEXT-ALIGN: right">15</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">2 6 7</TD><TD> </TD><TD style="TEXT-ALIGN: right">15</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">3 4 8</TD><TD> </TD><TD style="TEXT-ALIGN: right">15</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">3 5 7</TD><TD> </TD><TD style="TEXT-ALIGN: right">15</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">3 6 9</TD><TD> </TD><TD style="TEXT-ALIGN: right">18</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">4 5 6</TD><TD> </TD><TD style="TEXT-ALIGN: right">15</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">7 8 9</TD><TD> </TD><TD style="TEXT-ALIGN: right">24</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=SUM(A2)</TD></TR><TR><TD>B3</TD><TD>=SUM(A3)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
2) if i have data in column”A” and i want any 3 row totaling to 45,all row must be used ,and only once.i need an excel formula to give a result in Clomn B or like in Column C ;here is an example;
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> <TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>data</TD><TD>result1=45</TD><TD>or result2=45</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">1 2 3</TD><TD>A2,A12,A13</TD><TD>1 2 3,4 5 6,7 8 9</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">1 4 7</TD><TD>A3,A7,A11</TD><TD>1 4 7,2 5 8,3 6 9</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">1 5 9</TD><TD>A4,A8,A9</TD><TD>1 5 9,2 6 7,3 4 8</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">1 6 8</TD><TD>A5,A6,A10</TD><TD>1 6 8,2 4 9,3 5 7</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">2 4 9</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">2 5 8</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">2 6 7</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">3 4 8</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">3 5 7</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">3 6 9</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">4 5 6</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: right">7 8 9</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE></o>
Thanks
sezuh

 

[Ganjin]

no idea about the 2nd part, but for the first:
<TABLE style="WIDTH: 179pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=239 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>Data</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>Result</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 83pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=111>expected result</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21>1 2 3</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">6</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">6</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21>1 4 7</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">12</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">12</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21>1 5 9</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">15</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">15</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21>1 6 8</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">15</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">15</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21>2 4 9</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">15</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">15</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21>2 5 8</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">15</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">15</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21>2 6 7</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">15</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">15</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21>3 4 8</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">15</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">15</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21>3 5 7</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">15</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">15</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21>3 6 9</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">18</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">18</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21>4 5 6</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">15</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">15</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21>7 8 9</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">24</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">24


</TD></TR></TBODY></TABLE>
formula in Cell B2:
=SUMPRODUCT(IF(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)=" ",0,VALUE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))))
Ctrl+Shift+Enter

 

[sezuh]

Thanks Ganjin for your quick response and it works but i forget to mention some of my data contain two digit numbers for those the formula gives wrong result.if may i ask what

("1:"&LEN

stand for
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">260</TD><TD>09 11 17 19</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">29</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">261</TD><TD>09 12 14 15</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">23</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">262</TD><TD>09 12 17 20</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">22</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">263</TD><TD>09 13 16 17</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">28</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">264</TD><TD>10 11 12 14</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">11</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">265</TD><TD>10 11 15 17</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">17</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">266</TD><TD>10 11 19 20</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">15</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B260</TD><TD>{=SUMPRODUCT(IF(MID(A260,ROW(INDIRECT("1:"&LEN(A260))),1)=" ",0,VALUE(MID(A260,ROW(INDIRECT("1:"&LEN(A260))),1))))}</TD></TR><TR><TD>B261</TD><TD>{=SUMPRODUCT(IF(MID(A261,ROW(INDIRECT("1:"&LEN(A261))),1)=" ",0,VALUE(MID(A261,ROW(INDIRECT("1:"&LEN(A261))),1))))}</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
thanks again
Regards
sezuh

 

[Peter_SSs]

One way for the first part is to use a User-Defined Function.

<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> SumNums(<SPAN style="color:#00007F">ByVal</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN><br>    SumNums = Evaluate(Replace(s, " ", "+"))<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>


Use the function in the sheet like this, copied down.

Excel Workbook
	A	B
1	Data	Result
2	1 2 3	6
3	1 4 7	12
4	1 5 9	15
5	1 6 8	15
6	09 11 17 19	56
7	09 12 14 15	50
8	09 12 17 20	58
9	09 13 16 17	55
10	10 11 12 14	47
11	10 11 15 17	53
12	10 11 19 20	60
Sum UDF

 

[sezuh]

Thanks A mill. Peter that works perfect,much appreciated.
i dont suppose you have an idea about second query would you?
Have nice day,Off to work now.
Regards
sezuh

 

[Peter_SSs]

i dont suppose you have an idea about second query would you?

No,sorry.

 

[sezuh]

No problem Peter,
you helped me more than enough,much obliged.
thanks again
sezuh

 

[sezuh]

any suggestion for the second part please?
thanks

 

[sezuh]

Bump....??

 

[sezuh]

is there any tread,or similar problem you can point me to search or google
that would be much appreciated.
Thank you
sezuh