help column
 

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

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

Thanks Ken Wright, your formula is of great help
but how about if the " " is not found in the empty cells of the range?
i hope you can help
with many thanks again

"Ken Wright" wrote:

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

Ken already answered that question. Use Text to Columns.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"excelFan" wrote in message
...
Thanks Ken Wright, your formula is of great help
but how about if the " " is not found in the empty cells of the range?
i hope you can help
with many thanks again

"Ken Wright" wrote:

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