SUMPRODUCT using SEARCH with numbers and strings

[R0GU3]

I have data in cells that are number values and string values. The String values are #_ # with the # being any number. Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]3
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]1_4
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]2_10
[/TD]
[/TR]
</tbody>[/TABLE]

I want to SUM (3,1,4) and (2,3,2)

Here is what I have (Using Ctrl+Shift+Enter):

=SUMPRODUCT((LEFT(IF(ISTEXT(L31:L38), L31:L38, 0),IFERROR(SEARCH("_",L31:L38),0))-1))

 

[Scott T]

Try must use Ctrl+Shift+Enter

=SUM(IFERROR(LEFT(L31:L33,SEARCH("_",L31:L33)-1)+0,L31:L33))

 

[mart37]

left and search give a text as result
use VALUE to convert to a number

 

[mart37]

=sumproduct(if(isnumber(l31:l38),l31:l38;value(left(l31:l38,search("_",l31:l38)-1))))

 

[Nishant94]

Unknown
	A	B	C
1		3	2
2		1_4	3
3		4	2_10
4
5	Sum	8	7
Sheet6

Cell Formulas
Range	Formula
B5	=SUMPRODUCT(REPLACE(B1:B3,FIND("_",B1:B3&"_"),255,"")+0)

 

[Nishant94]

Or,

Unknown
	A	B	C
1		3	2
2		1_4	3
3		4	2_10
4
5	Sum	8	7
Sheet6

Cell Formulas
Range	Formula
B5	=SUMPRODUCT(INT(SUBSTITUTE(B1:B3,"_",".")+0))

 

[R0GU3]

Or,

Unknown

	A	B	C
Sum

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]

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

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]2_10[/TD]

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

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A9D08E]#A9D08E[/URL] , align: right"]7[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=SUMPRODUCT(INT(SUBSTITUTE(B1:B3,"_",".")+0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

All of your suggestions worked! This seemed to be the most simplest way to accomplish what I was going for. Didn't even think about making it a decimal number and just pulling the integer. This avoids using the function as an array too!

Thanks to all who responded.