Struggling With Array Formula . .

[Evagrius Ponticus]

Hi all,

I have a column, column E, with data. Each cell in the column looks like this:
21 - 8%
5 - 21%
17 - 3%

and so forth. I am trying to add the number to the left of each cell. In my example here, I must sum 21+5+17 =

I thought I had it with this CSE but it didn't work. Thanks for any help with this.

=SUM(VALUE(LEFT(E4:E16,FIND(" ",E4:E16,1))))

 

[sanrv1f]

Excel Workbook
	A
1	21-8%
2	5-21%
3	17-3%
4
5	43
Sheet1

Cell Formulas
Range	Formula
A5	=SUMPRODUCT( --LEFT(A1:A3,SEARCH(" -",A1:A3) -1))

 

[sanrv1f]

may be this, with a check

Excel Workbook
	A
1	21-8%
2	5-21%
3	17-3%
4
5	43
Sheet1

#VALUE!

 

[Evagrius Ponticus]

Hi - thanks for the formula but I receive the same type of error as with my initial formula "#Value"

 

[sanrv1f]

you need to remove the space preceding the "-"

=SUMPRODUCT( --LEFT(A1:A3,SEARCH("-",A1:A3) -1))

or

this with CSE

=SUMPRODUCT(IF(ISNUMBER(SEARCH("-",A1:A3)), --LEFT(A1:A3,SEARCH("-",A1:A3) -1),0)

 

[Evagrius Ponticus]

I see! Well done sanrv1f - both work now. Thank you - that is just good work!