Assuming example given is representative of all records:-

=SUMPRODUCT(--(LEFT(A2:A2000,FIND(" ",A2:A2000)-1)),--(MID(A2:A2000,FIND("
",A2:A2000)+1,LEN(A2:A2000))="name1"))

or switch name1 for a cell reference, BUT, its very bad practice indeed to
store numeric data in the same place as textual data and do what you are
doing. Far far better to use something like Data / Text To Columns and
split them up into two separate columns.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"excelFan" wrote in message
...
hi all,
is there a way to avoid greating a help column (to strip the numbers from
column A) as in my case where i have a range containing items like
A1=2000 name1
A2=1500 name2
A3=700 otherName
A4=500 name1
A5=""
A6=1500 name1
....
now i want to sumproduct for ( name1) only.
many thanks for any help