Sum of numbers with different prefix

[foster5476]

Hello,

New to the forum and have read thru the tips before posting and hope my chart was loaded correctly. I also search for an answer to my question but found nothing.

Well I work out of Autocad but my tables are imported data from excel spreadsheets.

So what I am trying to do is get the my table to get the sum of the numbers with the S- or B- in front of the number and disregard the -L after the number.

I am not able to remove the prefix or suffix on the numbers. So is there a way to get only the number sum in a function form? Basically giving me 24+25+143+144=336

Thanks I hope this made sense.


[TABLE="width: 206"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 3"]LIST[/TD]
[/TR]
[TR]
[TD]QTY.[/TD]
[TD]MARK[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]s-24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]b-25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]s-143-L[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]B-144-L[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="colspan: 2"]X[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Other info for me[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Other info for me[/TD]
[/TR]
[TR]
[TD="colspan: 3"]336 <--where I need the total [/TD]
[/TR]
</tbody>[/TABLE]

 

[63falcondude]

This worked for your example given. Are there any instances where this will not work?

Excel 2010
	A	B
1	LIST
2	QTY.	MARK
3	1	s-24
4	1	b-25
5	1	s-143-L
6	1	B-144-L
7	4	X
8
9	336
Sheet1

Cell Formulas
Range	Formula
A9	{=SUM(IFERROR(SUBSTITUTE(MID(B3:B7,FIND("-",B3:B7)+1,LEN(B3:B7)),"-L","")+0,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[foster5476]

Thanks for the quick response let me give it a shot.

 

[Tetra201]

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=SUM(IFERROR(--MID(SUBSTITUTE(B3:B7,"-L",""),3,99),0))

 

[foster5476]

When I post it in my Autocadd I get ####

I tried in my excel 2013 also and for some reason ctrl, shift, enter doesn't work but was able to insert it keeping the format. It came out looking just like how you posted with the colors and the brackets. Am I doing something wrong? Im not work a lot in excel if there is something I need to tinker with.

Thanks in advance.

This worked for your example given. Are there any instances where this will not work?

Excel 2010

	A	B
LIST
QTY.	MARK
s-24
b-25
s-143-L
B-144-L
X

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]336[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A9[/TH]
[TD="align: left"]{=SUM(IFERROR(SUBSTITUTE(MID(B3:B7,FIND("-",B3:B7)+1,LEN(B3:B7)),"-L","")+0,""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[foster5476]

Tetra when I put yours in im getting 0.

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=SUM(IFERROR(--MID(SUBSTITUTE(B3:B7,"-L",""),3,99),0))

 

[foster5476]

Sorry I messed up inputting the formula.

My ctrl shift enter isnt working correctly I guess but I got it.

Thank I really appreciate it.

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=SUM(IFERROR(--MID(SUBSTITUTE(B3:B7,"-L",""),3,99),0))

 

[foster5476]

Is there a reason why it wouldnt work in Autocadd and it does in excel? Must be the way im putting in the equation????

 

[foster5476]

63falcondude I got yours to work also. Just not in Autocad yet if you have any pointers on inserting with other programs that may do the trick.

 

[63falcondude]

63falcondude I got yours to work also. Just not in Autocad yet if you have any pointers on inserting with other programs that may do the trick.

Sorry, I am not familiar with Autocad.