Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort by row instead of by column? | Excel Worksheet Functions | |||
Format cell in column B based on value in the next cell (column c) | Excel Discussion (Misc queries) | |||
Comparing Cells and Displaying Data | Excel Worksheet Functions | |||
Copying the contents of a column into a chart | Excel Worksheet Functions | |||
How to calculate the data in excel 2002 including only the last 9. | Excel Worksheet Functions |