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

 

[pgc01]

Hi

Now I understand why you wanted to find the 20 million combinations here:

http://www.mrexcel.com/forum/showthread.php?p=2678842#post2678842

You can use the code I posted there to solve the second part of the question.

I still say that it is not necessary to write the combinations. What you should do is to use the code I posted and as each combination is built you check if the sum of the numbers in the 3 elements is 45. When that happens, that's when you should write the combination.

 

[texasaswang]

Is this a homework assignment?

 

[sezuh]

Hi,
Texasaswang no it’s not homework ,unfortunately its a hobby.
Pedro thanks for your suggestion,but i did not understand this;
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
After getting the result of” Combin(495,3)which is Combin(12,4)”i’ll discard any row not starting with number “1”then any row contain duplicate numbers ,.....in short i’ll will end up with only 165 rows contain all 12 numbers in them out of “20092215”rows.that very huge task unless someone out of you good people come up with Vb code to it????this how i’m going to try to get my result;
<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><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">1 2 3</TD><TD style="TEXT-ALIGN: right">3 6 9</TD><TD style="TEXT-ALIGN: right">7 8 9</TD><TD style="FONT-SIZE: 12pt; COLOR: #00ccff; FONT-FAMILY: Times New Roman">1 2 3, 3 6 9, 7 8 9</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">1 2 3</TD><TD style="TEXT-ALIGN: right">4 5 6</TD><TD style="TEXT-ALIGN: right">7 8 9</TD><TD style="FONT-SIZE: 12pt; COLOR: #00ccff; FONT-FAMILY: Times New Roman">1 2 3, 4 5 6, 7 8 9</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">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="TEXT-ALIGN: right">1 5 9</TD><TD style="TEXT-ALIGN: right">2 6 7</TD><TD style="TEXT-ALIGN: right">3 4 8</TD><TD style="FONT-SIZE: 12pt; COLOR: #00ccff; FONT-FAMILY: Times New Roman">1 5 9, 2 6 7, 3 4 8</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">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">4</TD><TD style="TEXT-ALIGN: right">1 5 9</TD><TD style="TEXT-ALIGN: right">2 6 7</TD><TD style="TEXT-ALIGN: right">3 5 7</TD><TD style="FONT-SIZE: 12pt; COLOR: #00ccff; FONT-FAMILY: Times New Roman">1 5 9, 2 6 7, 3 5 7</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">1 6 8</TD><TD style="TEXT-ALIGN: right">2 4 9</TD><TD style="TEXT-ALIGN: right">3 5 7</TD><TD style="FONT-SIZE: 12pt; COLOR: #00ccff; FONT-FAMILY: Times New Roman">1 6 8, 2 4 9, 3 5 7</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">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="TEXT-ALIGN: right">1 6 8</TD><TD style="TEXT-ALIGN: right">2 4 9</TD><TD style="TEXT-ALIGN: right">3 6 9</TD><TD style="FONT-SIZE: 12pt; COLOR: #00ccff; FONT-FAMILY: Times New Roman">1 6 8, 2 4 9, 3 6 9</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">1 4 7</TD><TD style="TEXT-ALIGN: right">2 5 8</TD><TD style="TEXT-ALIGN: right">3 4 8</TD><TD style="FONT-SIZE: 12pt; COLOR: #00ccff; FONT-FAMILY: Times New Roman">1 4 7, 2 5 8, 3 4 8</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">1 4 7</TD><TD style="TEXT-ALIGN: right">2 5 8</TD><TD style="TEXT-ALIGN: right">3 5 7</TD><TD style="FONT-SIZE: 12pt; COLOR: #00ccff; FONT-FAMILY: Times New Roman">1 4 7, 2 5 8, 3 5 7</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">1 4 7</TD><TD style="TEXT-ALIGN: right">2 5 8</TD><TD style="TEXT-ALIGN: right">3 6 9</TD><TD style="FONT-SIZE: 12pt; COLOR: #00ccff; FONT-FAMILY: Times New Roman">1 4 7, 2 5 8, 3 6 9</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">1 4 7, 2 5 8, 3 6 9</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>D1</TD><TD>=A1&","&B1&","&C1</TD></TR><TR><TD>E1</TD><TD>=IF(ISERROR(SUMPRODUCT(FIND({1;2;3;4;5;6;7;8;9},D1)))=FALSE,D1,"")</TD></TR><TR><TD>D2</TD><TD>=A2&","&B2&","&C2</TD></TR><TR><TD>E2</TD><TD>=IF(ISERROR(SUMPRODUCT(FIND({1;2;3;4;5;6;7;8;9},D2)))=FALSE,D2,"")</TD></TR><TR><TD>D3</TD><TD>=A3&","&B3&","&C3</TD></TR><TR><TD>E3</TD><TD>=IF(ISERROR(SUMPRODUCT(FIND({1;2;3;4;5;6;7;8;9},D3)))=FALSE,D3,"")</TD></TR><TR><TD>D4</TD><TD>=A4&","&B4&","&C4</TD></TR><TR><TD>E4</TD><TD>=IF(ISERROR(SUMPRODUCT(FIND({1;2;3;4;5;6;7;8;9},D4)))=FALSE,D4,"")</TD></TR><TR><TD>D5</TD><TD>=A5&","&B5&","&C5</TD></TR><TR><TD>E5</TD><TD>=IF(ISERROR(SUMPRODUCT(FIND({1;2;3;4;5;6;7;8;9},D5)))=FALSE,D5,"")</TD></TR><TR><TD>D6</TD><TD>=A6&","&B6&","&C6</TD></TR><TR><TD>E6</TD><TD>=IF(ISERROR(SUMPRODUCT(FIND({1;2;3;4;5;6;7;8;9},D6)))=FALSE,D6,"")</TD></TR><TR><TD>D7</TD><TD>=A7&","&B7&","&C7</TD></TR><TR><TD>E7</TD><TD>=IF(ISERROR(SUMPRODUCT(FIND({1;2;3;4;5;6;7;8;9},D7)))=FALSE,D7,"")</TD></TR><TR><TD>D8</TD><TD>=A8&","&B8&","&C8</TD></TR><TR><TD>E8</TD><TD>=IF(ISERROR(SUMPRODUCT(FIND({1;2;3;4;5;6;7;8;9},D8)))=FALSE,D8,"")</TD></TR><TR><TD>D9</TD><TD>=A9&","&B9&","&C9</TD></TR><TR><TD>E9</TD><TD>=IF(ISERROR(SUMPRODUCT(FIND({1;2;3;4;5;6;7;8;9},D9)))=FALSE,D9,"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<o> </o>
In this tread #10 post there is similar problem and AdamL gave excel formula solution,but its not working with this problem ?
http://www.mrexcel.com/forum/showthread.php?t=514797
please if any one got easier solution would be appreciated.
Thank again
Sezuh

 

[sezuh]

Hi,
Is there any other way i can use this formula without "concatenate A1:C1".????
=IF(ISERROR(SUMPRODUCT(FIND({1;2;3;4;5;6;7;8;9},D1)))=FALSE,D1,"")
Pedro,i forgot to mention how would you do this,i'm sorry i did not get this;because you can not see anything when the code is running.

I still say that it is not necessary to write the combinations. What you should do is to use the code I posted and as each combination is built you check if the sum of the numbers in the 3 elements is 45. When that happens, that's when you should write the combination.

thanks
kind regards
sezuh

 

[sezuh]

Hi, is there a way to implement this formula to "3" columns at the sametime instead of one?
=IF(ISERROR(SUMPRODUCT(FIND({1;2;3;4;5;6;7;8;9},D1)))=FALSE,D1,"")
thanks for any help.
sezuh

 

[sezuh]

Bump........?

 

[sezuh]

Help please.....

 

[sezuh]

bump.........?