Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct?
Good afternoon:
I have a list of data (see below) LTL 4000-403000-430015-716 2005 3 $8.81 LTL 4000-403000-430015-403 2003 11 $15.59 SMPK 4000-403000-430015-403 2005 6 $17.80 SMPK 4000-403000-430015-403 2005 9 $27.28 SMPK 4000-403000-430015-716 2005 6 $32.12 LTL 4000-403000-430015-404 2004 6 $33.00 SMPK 4000-403000-430015-716 2005 5 $33.74 LTL 4000-403000-430015-403 2003 10 $43.00 In a separate portion of the spreadsheet, I would like to have 4 cells filled with: - LTL - 4000-403000-430015-716 - 2005 - 3 And I would like excel to return the value $8,81 in a fifth cell... What is the formula I need to use to obtain the value in the 5th cell based on the value I have in 4 different cells What if There are several rows with the same values (LTL, 4000-403000-430015-716, 2005, 3)... What would be the sumproduct statement like? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct?
Not sure what sumproduct has to do with your question.
Just use Data--Text to columns and select space as your column delimiter. Dave -- Brevity is the soul of wit. "MIchel Khennafi" wrote: Good afternoon: I have a list of data (see below) LTL 4000-403000-430015-716 2005 3 $8.81 LTL 4000-403000-430015-403 2003 11 $15.59 SMPK 4000-403000-430015-403 2005 6 $17.80 SMPK 4000-403000-430015-403 2005 9 $27.28 SMPK 4000-403000-430015-716 2005 6 $32.12 LTL 4000-403000-430015-404 2004 6 $33.00 SMPK 4000-403000-430015-716 2005 5 $33.74 LTL 4000-403000-430015-403 2003 10 $43.00 In a separate portion of the spreadsheet, I would like to have 4 cells filled with: - LTL - 4000-403000-430015-716 - 2005 - 3 And I would like excel to return the value $8,81 in a fifth cell... What is the formula I need to use to obtain the value in the 5th cell based on the value I have in 4 different cells What if There are several rows with the same values (LTL, 4000-403000-430015-716, 2005, 3)... What would be the sumproduct statement like? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct?
=INDEX(E1:E1000,MATCH(1,(A1:A1000=M1)*(B1:B1000=M2 )*(C1:C1000=M3)*(D1:D1000=
M4),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "MIchel Khennafi" wrote in message ... Good afternoon: I have a list of data (see below) LTL 4000-403000-430015-716 2005 3 $8.81 LTL 4000-403000-430015-403 2003 11 $15.59 SMPK 4000-403000-430015-403 2005 6 $17.80 SMPK 4000-403000-430015-403 2005 9 $27.28 SMPK 4000-403000-430015-716 2005 6 $32.12 LTL 4000-403000-430015-404 2004 6 $33.00 SMPK 4000-403000-430015-716 2005 5 $33.74 LTL 4000-403000-430015-403 2003 10 $43.00 In a separate portion of the spreadsheet, I would like to have 4 cells filled with: - LTL - 4000-403000-430015-716 - 2005 - 3 And I would like excel to return the value $8,81 in a fifth cell... What is the formula I need to use to obtain the value in the 5th cell based on the value I have in 4 different cells What if There are several rows with the same values (LTL, 4000-403000-430015-716, 2005, 3)... What would be the sumproduct statement like? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct?
If you want to add row 5 whenever the other 4 rows match the designated
criteria...... =SUMPRODUCT(--(A1:A1000=M1),--(B1:B1000=M2),--(C1:C1000=M3),--(D1:D1000=M4),E1:E1000) "MIchel Khennafi" wrote: Good afternoon: I have a list of data (see below) LTL 4000-403000-430015-716 2005 3 $8.81 LTL 4000-403000-430015-403 2003 11 $15.59 SMPK 4000-403000-430015-403 2005 6 $17.80 SMPK 4000-403000-430015-403 2005 9 $27.28 SMPK 4000-403000-430015-716 2005 6 $32.12 LTL 4000-403000-430015-404 2004 6 $33.00 SMPK 4000-403000-430015-716 2005 5 $33.74 LTL 4000-403000-430015-403 2003 10 $43.00 In a separate portion of the spreadsheet, I would like to have 4 cells filled with: - LTL - 4000-403000-430015-716 - 2005 - 3 And I would like excel to return the value $8,81 in a fifth cell... What is the formula I need to use to obtain the value in the 5th cell based on the value I have in 4 different cells What if There are several rows with the same values (LTL, 4000-403000-430015-716, 2005, 3)... What would be the sumproduct statement like? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding "OR" to a Sumproduct Formula | Excel Worksheet Functions | |||
Need help with sumproduct and dynamic ranges | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |