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

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

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

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