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:

3	2
1_4	3
4	2_10

<tbody>
</tbody>

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
1		3	2
2		1_4	3
3		4	2_10
4
5	Sum	8	7

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula B5 =SUMPRODUCT(INT(SUBSTITUTE(B1:B3,"_",".")+0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.